0

I have a column which can contain over 20 different values and they are seprated by semicolon. for example : A;B;C;D;E;F;G;H;...., I need to split all the values in new columns. I tried to split it with SPLIT_PART(), but it seems i need to sepcify which position do i want from the string. But in my case, there are too many values and it's not very effcient to do like this.

I wonder if there's anyway that I can split them by ; and all value will be saved in a new column?

timo
  • 13
  • 2
  • I suggest just reloading your table using Postgres' CSV loading tool. Then the data will arrive in a table with the columns you expect. – Tim Biegeleisen Nov 18 '21 at 11:34
  • Does this answer your question? [Split comma separated column data into additional columns](https://stackoverflow.com/questions/8584967/split-comma-separated-column-data-into-additional-columns) – nbk Nov 18 '21 at 11:37
  • If you want multiple columns there is no way around writing one expression for each column you want in the output. –  Nov 18 '21 at 11:39
  • okya thanks you for the input! I thought there could be an easy way to do that :( , thanks though! – timo Nov 18 '21 at 13:01

1 Answers1

1

Split the string into an array and select array elements. A CTE stands for the table.
Here is an illustration for 10 columns. Still somewhat verbose but of a regular nature.

with the_table(s) as 
(
 values
 ('A01;B01;C01;D01;E01;F01;G01;H01;I01;J01'),
 ('A02;B02;C02;D02;E02;F02;G02;H02;I02;J02'),
 ('A03;B03;C03;D03;E03;F03;G03;H03;I03;J03')
),
arr(a) as 
(
 select string_to_array(s,';') from the_table
)
select 
  a[1] c1,a[2] c2,a[3] c3,a[4] c4,a[5]  c5,
  a[6] c6,a[7] c7,a[8] c8,a[9] c9,a[10] c10 -- ...
from arr;
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10
A01 B01 C01 D01 E01 F01 G01 H01 I01 J01
A02 B02 C02 D02 E02 F02 G02 H02 I02 J02
A03 B03 C03 D03 E03 F03 G03 H03 I03 J03
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • so i have to write the 1 to 10 index to get the value? I thought therei will be a more effcient way. But thanks! – timo Nov 18 '21 at 13:00
  • Yes, but you do not have to use offsets and lengths. Just a regular sequence of numbers. And if the offsets/lengths change from record to record it will still work correctly. – Stefanov.sm Nov 18 '21 at 13:02