Split a string in characters SQL

Question

How can I split a string in characters and add a new line after each character in PostgreSQL

For example

num  desc
 1    Hello
 2    Bye

num  desc
 1    H
      e
      l
      l
      o

 2    B
      y 
      e

You could probably use the functions on this page: http://www.postgresql.org/docs/9.1/static/functions-string.html — Thom Wiggers, Mar 10 '14 at 10:22

score 6 · Accepted Answer · edited May 23 '17 at 12:05

select num, regexp_split_to_table(descr,'')
from the_table
order by num;

SQLFiddle: http://sqlfiddle.com/#!15/13c00/4

The order of the characters is however not guaranteed and achieving that is a bit complicated.

Building on Erwin's answer regarding this problem:

select case 
         when row_number() over (partition by id order by rn) = 1 then id 
         else null
       end as id_display, 
       ch_arr[rn]
from (
  select *, 
         generate_subscripts(ch_arr, 1) AS rn
  from (
    select id, 
           regexp_split_to_array(descr,'') as ch_arr
    from data
  ) t1
) t2
order by id, rn;

Edit:

If you just want a single string for each id, where the characters are separated by a newline, you can use this:

select id, 
       array_to_string(regexp_split_to_array(descr,''), chr(10))
from data
order by id

Thanks I can use this, what I was looking for is to have the string split in characters and separated by a newline `"chr(10)"` in the same cell, but this should work too. — Carlos Escalera Alonso, Mar 10 '14 at 11:31

Split a string in characters SQL

1 Answers1