7

I have table with string data like this:

id | string_data
1  | red;green;blue
2  | orange
3  | purple;cyan

And I need split string data to items with row numbers:

id | num | item
1  | 1   | red
1  | 2   | green
1  | 3   | blue
2  | 1   | orange
3  | 1   | purple
3  | 2   | cyan

I know regexp_split_to_table() but I have problem to combine it with row_number(). Does anybody know how to solve this? Thanks!

Cateh
  • 71
  • 1
  • 1
  • 2
  • 2
    You could use the [`WITH ORDINALITY` clause](https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS) instead, for a more reliable solution. – pozs May 22 '17 at 15:49
  • 1
    Posible duplicated of https://dba.stackexchange.com/questions/27279/how-to-preserve-the-original-order-of-elements-in-an-unnested-array – Juan Carlos Oropeza May 22 '17 at 15:50
  • 1
    This is better dealt with by normalizing your schema. – Gurwinder Singh May 22 '17 at 15:59

2 Answers2

13

If you don't need a regex it's more efficient to use string_to_array() instead of regexp_split_to_table(). To get the array index, use with ordinality 

select t.id, 
       x.idx,
       x.word
from the_table t, 
     unnest(string_to_array(string_data, ';')) with ordinality as x(word, idx)
order by t.id, x.idx;
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
7
select
    id,
    row_number() over (partition by id) as rn,
    s
from (
    select
        id,
        regexp_split_to_table(s, ';') as s
    from t
) r
;
 id | rn |   s    
----+----+--------
  1 |  1 | red
  1 |  2 | green
  1 |  3 | blue
  2 |  1 | orange
  3 |  1 | purple
  3 |  2 | cyan
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260