CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Tomas Greif
Tomas Greif

Reputation: 22653

Split string after every nth character

Is there a way how to split string after every nth character in PostgreSQL? I thought regexp_split_to_array can be used to do this:

select unnest(regexp_split_to_array('abcdefgh', E'...regexp here...'));

Example input: abcdefgh

Required output (split after every 2nd character):

ab
cd
ef
gh

Required output (split after every 3rd character):

abc
def
gh

What regexp will do this? Is there any other solution?

Upvotes: 11

Views: 7619

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324511

Use substring and generate_series:

regress=> select substring('abcdefgh' from n for 2) from generate_series(1, length( 'abcdefgh' ), 2) n;
 substring 
-----------
 ab
 cd
 ef
 gh
(4 rows)

regress=> select substring('abcdefgh' from n for 3) from generate_series(1, length( 'abcdefgh' ), 3) n;
 substring 
-----------
 abc
 def
 gh
(3 rows)

This is trivially wrapped into an inlineable SQL function:

CREATE OR REPLACE FUNCTION string_nchars(text, integer) RETURNS setof text AS $$
SELECT substring($1 from n for $2) FROM generate_series(1, length($1), $2) n;
$$ LANGUAGE sql IMMUTABLE;

Usage:

regress=> SELECT string_nchars('abcdefgh',3);
 string_nchars 
---------------
 abc
 def
 gh
(3 rows)

Upvotes: 15

jjanes
jjanes

Reputation: 44227

You can use lookaheads. Lookbehind would be better, but are not implemented.

This only works if the length of the string is even (or a multiple of the split size), and might be inefficient for large strings.

select unnest(regexp_split_to_array('abcdefgh', E'(?=(..)+$)'));

If it is not a multiple, then something like:

select reverse(unnest) from unnest(regexp_split_to_array(reverse('abcdefgh'), E'(?=(...)+$)'));

But I'd probably install plperl and then do it in Perl.

Upvotes: 6

Related Questions