Splitting characters after regexp_split_to_array

Question

select name, (regexp_split_to_array(name, '')) from table

The result is {a,b,c,d,e}. Is it possible to split that up to individual rows and group by name so that it looks like:

a 1
b 2
c 3
d 4
e 5

Answer 1

You are looking for the unnest function:

select name, unnest(regexp_split_to_array(name, '')) from table

Answer 2

Use unnest() with row_number() window function:

WITH test_table(name) AS ( VALUES
  ('abcde')
)
SELECT *,row_number() OVER () AS row FROM (
  SELECT unnest(regexp_split_to_array(name,'')) AS name FROM test_table
) t;

Result:

 name | row 
------+-----
 a    |   1
 b    |   2
 c    |   3
 d    |   4
 e    |   5
(5 rows)

There is also great answer at SO:

• PostgreSQL unnest() with element number

Answer 3

If you are on 9.4 or later you can use with ordinality:

SELECT name, u.*
FROM the_table
  cross join lateral unnest(regexp_split_to_array(name,'')) with ordinality as u(ch, nr)