Divide sql results in rows through delimiter

Question

Divide sql results in rows through delimiter

Navigation

#1 by (0 votes)

1

Good afternoon, guys. I have a difficulty to do a select in a postgres DB here.

For some reason, the programmer invented creating a field where, if the user enters two notes at the same time, the system writes in the bank only one line for that release but separating the two notes by a comma in the field recn_numerodcto .

So, if I run the select below in the database,

 select  recn_data, recn_unid, recn_numerodcto from recnota

The result will be:

recn_data | recn_unid | recn_numerodcto

25-09-2018 | 001 | 123, 321

Is there a way I can break this result to generate a row for each note? Ex:

recn_data | recn_unid | recn_numerodcto

25-09-2018 | 001 | 123

25-09-2018 | 001 | 321

postgresql select

asked by anonymous 25.09.2018 / 18:58

1 answer

Pass start and end of a vector to the sort function using c ++ method overload Center a NAV - With video background

score 0 · Accepted Answer

Use the combination of the string_to_array function with the unnest function.

string_to_array : converts the string entered in the first parameter to a array by separating the elements according to the delimiter given in the second parameter.

unnest : Expands an array to a set of rows .

select
    recn_data, 
    recn_unid, 
    unnest(string_to_array(recn_numerodcto,','))
from recnota

I added some records to the sample database:

insert into recnota (recn_data,recn_unid,recn_numerodcto)
values
    ('2018-09-25','001','123,321'),
    ('2018-09-26','001','1234,4321');

Running the first code will return:

┌────────────┬───────────┬────────┐
│ recn_data  │ recn_unid │ unnest │
├────────────┼───────────┼────────┤
│ 2018-09-25 │ 001       │ 123    │
│ 2018-09-25 │ 001       │ 321    │
│ 2018-09-26 │ 001       │ 1234   │
│ 2018-09-26 │ 001       │ 4321   │
└────────────┴───────────┴────────┘
(4 registros)

link