postgreSQL: field separated by semicolon

Question

I have a field called client_no_all:

client_no_all 
14521;555555;636582142;

I want to separate the values and assign them to client_no:

client_no
14521 
555555 
636582142

Should I use a cursor to loop or pivot?

pgsql ist not a plsql, so what do you need? – hotfix Feb 19 '20 at 12:30 — hotfix, Feb 19 '20 at 12:30

Mihawk · Answer 1 · 2020-02-20T08:05:38.333

0

You can use following query. The function string_to_array() splits a string by a delimiter into an array. With the function unnest() you can expand an array to a set of rows.

SELECT unnest(string_to_array('1;2;3;4;5', ';')) AS val

edited Feb 20 '20 at 08:05

answered Feb 19 '20 at 14:14

Mihawk

815
3
14
31

This may answer the question. However, code only answers are not as useful as answers that document the code or have an detailed explanation on why this code is the solution to the question. – RyanNerd Feb 19 '20 at 19:29
@RyanNerd you are right just saw the question and had the same problem two days ago so just shared the solution – Mihawk Feb 19 '20 at 20:03

postgreSQL: field separated by semicolon

1 Answers1