Split string after every nth character

Question

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?

Answer 1

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)