Split comma separated column data into additional columns

Question

I have comma separated data in a column   Column  -------  a b c d    I want to split the comma separated data into multiple columns to get this output   Column1  Column2 Column3 Column4  -------  ------- ------- ------- a        b       c       d    How can this be achieved

User · Answer

split part   does what you want in one step   SELECT split part col       1  AS col1        split part col       2  AS col2        split part col       3  AS col3        split part col       4  AS col4 FROM   tbl    Add as many lines as you have items in col  the possible maximum   Columns exceeding data items will be empty strings

User · Answer

You can use split function       SELECT       select top 1 item from dbo Split FullName      where id 1   Column1       select top 1 item from dbo Split FullName      where id 2   Column2       select top 1 item from dbo Split FullName      where id 3   Column3       select top 1 item from dbo Split FullName      where id 4   Column4      FROM MyTbl

User · Answer

If the number of fields in the CSV is constant then you could do something like this   select a 1   a 2   a 3   a 4  from       select regexp split to array  a b c d          as dt a    For example     gt  select a 1   a 2   a 3   a 4  from  select regexp split to array  a b c d         as dt a    a   a   a   a  --- --- --- ---  a   b   c   d  1 row    If the number of fields in the CSV is not constant then you could get the maximum number of fields with something like this   select max array length regexp split to array csv        1   from your table   and then build the appropriate a 1   a 2        a M  column list for your query  So if the above gave you a max of 6  you d use this   select a 1   a 2   a 3   a 4   a 5   a 6  from       select regexp split to array csv           from your table   as dt a    You could combine those two queries into a function if you wanted   For example  give this data  that s a NULL in the last row      gt  select   from csvs       csv      -------------  1 2 3  1 2 3 4  1 2 3 4 5 6   4 rows     gt  select max array length regexp split to array csv        1   from csvs   max  -----    6  1 row     gt  select a 1   a 2   a 3   a 4   a 5   a 6  from  select regexp split to array csv       from csvs  as dt a    a   a   a   a   a   a  --- --- --- --- --- ---  1   2   3             1   2   3   4         1   2   3   4   5   6                        4 rows    Since your delimiter is a simple fixed string  you could also use string to array instead of regexp split to array   select     from       select string to array csv           from csvs   as dt a     Thanks to Michael for the reminder about this function   You really should redesign your database schema to avoid the CSV column if at all possible  You should be using an array column or a separate table instead

[postgresql] Split comma separated column data into additional columns

Examples related to postgresql

Examples related to split

Examples related to delimiter

Examples related to postgresql-8.4