How to iterate through chars of a string in Postgresql?

Question

I have to create a function in PostgreSQL to show the pair characters of a string passed as a parameter.

What I thought was to loop through the chars of the string, like I would do it in C or Java or whatever, but I don't know how to access to a character individually, what would be str[i], for example, being str a string, and i the position of the char.

What I mean by "pair characters" is characters in pair positions: that is, characters in the string whose position modulo 2 = 0. If the input parameter is "String", the characters showed by the function would be "t" (ie position 2, starting from position 1), and "i" (ie position 4), "g"(position 6).

Please show some input and the expected result output. Also, why? What's the underlying problem you're trying to solve by doing this? — Craig Ringer
– Craig Ringer, Commented Jul 3, 2014 at 1:16
@Craig Ringer There is no other problem that the one I described. I'm learning PostgreSQL now, and it's a simple exercise ;) An example of the function input and output would be this: "FunctionPairChars('thisIsAString')" Output: hssSrn — xecollons
– xecollons, Commented Jul 3, 2014 at 8:49

Evgeny Nozdrev · Accepted Answer · 2019-12-24 12:18:42Z

11

Iteration over an array (best way):

FOREACH ch IN ARRAY regexp_split_to_array('hello', '')
LOOP
  ...
END LOOP;

Iteration over a table:

FOR row_record IN
  SELECT tbl.col
  FROM regexp_split_to_table('hello', '') AS tbl(col)
LOOP
  ...
END LOOP;

will iterate over the table tbl with column col:

 col
-----
 h
 e
 l
 l
 o
(5 rows)

Access to the value over row_record.col.

Not sure why you want to "iterate" over strings. Such a task would IMO be better done in a programming language.

edited Dec 24, 2019 at 12:18

Evgeny Nozdrev

1,56613 silver badges15 bronze badges

answered Jul 3, 2014 at 9:54

DrColossos

13.1k3 gold badges49 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xecollons Over a year ago

I think that will be great for my function! Thanks! I'll try this afternoon and if it works, I will mark your answer as the answer. About how I write it in PostgreSQL, as I said, it's because it's a simple exercise from a book, and that is the specification.

Lauro235 Over a year ago

I have a really similar question. I'm trying to loop through a string of characters, but I'm struggling to understand this... FOREACH ch IN ARRAY regexp_split_to_array('hello', '') LOOP ... END LOOP; It'd be super helpful if you could provide an example.

Craig Ringer · Accepted Answer · 2014-07-04 02:29:11Z

3

Your problem specification is really, really unclear. So it's hard to guess what you really want.

Say you want pairs of characters, such that the input string abcd returns ab,bc,cd. That's as good a guess as any from the vague description provided.

If that's what you want, here's one possible solution:

CREATE OR REPLACE FUNCTION charpairs(text) RETURNS SETOF text AS $$
SELECT substring($1 from x for 2) FROM generate_series(1, length($1)-1) x;
$$
LANGUAGE sql
IMMUTABLE;

usage:

regress=> SELECT * FROM charpairs('abcd');
 charpairs 
-----------
 ab
 bc
 cd
(3 rows)

Not what you wanted? Define the problem properly. Usually half the challenge in programming is properly defining a problem. Once you do that well, the code often almost writes its self. Start with the inputs and desired outputs. Work from the ends into the middle.

After update in comments:

You want chars with position % 2 == 1. So one approach is:

CREATE OR REPLACE FUNCTION everysecond(text) RETURNS SETOF text AS $$
SELECT substring($1 from x for 1) FROM generate_series(2, length($1), 2) x;
$$
LANGUAGE sql
IMMUTABLE;

possibly with a string_agg in there if you want a string not a set as a result.

edited Jul 4, 2014 at 2:29

answered Jul 3, 2014 at 10:28

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

1 Comment

xecollons Over a year ago

My bad, sometimes it's hard to express myself in English where it is not my native language. Sorry about that. What I meant with "pair characters" was "characters in pair positions", that is, characters in the string whose position MOD 2 = 0. As I said, if the input parameter is "String", the characters showed by the function would be "t"(position 2, starting from position 1), "i"(position 4), "g"(position 6). Thanks anyway for the help.

Collectives™ on Stack Overflow

How to iterate through chars of a string in Postgresql?

2 Answers 2

Iteration over an array (best way):

Iteration over a table:

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Iteration over an array (best way):

Iteration over a table:

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related