SQL Split Comma Seperated Strings Into Multiple Columns

Question

I am newish to SQL to please excuse any ignorance. I have a table, called 'temp' that contains one Field, (called Field1) with rows and rows of long comma seperated strings, thus:

Temp table
Field1
ABC123,1,Z,some text
ABC456,1,q,some text
ABC789,1,r,some text
ABC111,1,y,some text

I then have a another table, called Detail, with 4 headings. So how do I insert the above from the temp table to the temp table to look like this:

Detail Table
Field1          Field 2    Field 3          Field 4
ABC123         1             Z                 some text
ABC456         1             q                 some text 
ABC789          1            r                  some text
ABC111         1            y                 some text

I will then be using reporting reporting services to report on the detail table. Do I need to call a function? I've been reading up and some people are saying an XML function is better? All my other bits of code is in a SP so how do I call it from here?

Thank you in advance for any help.

Regards,

Michael

You don't specify an RDBMS, but there are multiple duplicates of this question, all shown on the right-hand side: SQL Server (http://stackoverflow.com/questions/9589282/converting-comma-delimited-string-to-multiple-columns-in-sql-server?rq=1 and http://stackoverflow.com/questions/16722307/sql-server-splitting-string-by-and-put-each-split-element-into-a-different?rq=1), MySQL (http://stackoverflow.com/questions/6823924/split-column-string-into-multiple-columns-strings?rq=1), etc.... — LittleBobbyTables - Au Revoir, Jun 11 '13 at 13:13
Why dont you put it into .csv format and pipe it into a table using SSIS - Does it have to be held within a temporary table? — JsonStatham, Jun 11 '13 at 13:15
This question does not show any research effort. It is important to **do your homework**. Tell us what you found and ***why*** it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. [FAQ](http://stackoverflow.com/questions/how-to-ask). — Kermit, Jun 11 '13 at 13:15
possible duplicate of [SQL Server 2008 how to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/sql-server-2008-how-to-split-a-comma-separated-value-to-columns) — LittleBobbyTables - Au Revoir, Jun 11 '13 at 13:20
I had to put it in a temp table to start as the file is coming from a legacy system whereby some of the fields have double quotes and some do not. SQL specifies that either none or all of the values in a data field are enclosed in quotation marks (""), so I first import into a temp table, then cleanup to remove the " ". — Michael, Jun 11 '13 at 13:25

score 0 · Answer 1 · answered Jun 11 '13 at 13:15

The database you are using makes a big difference. Here is an example of how to do it in MySQL:

insert into detail(field1, field2, field3, field4)
    select substring_index(field1, 1),
           reverse(substring_index(reverse(substring_index(field1, 2)))),
           reverse(substring_index(reverse(substring_index(field1, 3)))),
           reverse(substring_index(reverse(substring_index(field1, 4))))
    from tempTable t

The complex expression for the 2-4 fields is just a way of extracting the nth item from the list.

SQL Split Comma Seperated Strings Into Multiple Columns

1 Answers1