Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL SPLIT COMMA STRING INTO MULTIPLE COLUMNS

Author  Topic 

michaelhutcheson
Starting Member

2 Posts

Posted - 2013-06-11 : 11:33:04
I am newish to SQL (SQL Server 2008) so 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. I've been playing about with the below in my SP but it isn't working:

INSERT INTO Detail
WITH Field1 (Field, xmlfields)
AS
(
SELECT Field1 AS Field,
CONVERT(XML,'<Fields><field>'
+ REPLACE(Field1,',', '</field><field>') + '</field></Fields>') AS xmlfields
FROM Temp
)

I've 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 " ".

Thank you in advance for any help.


Regards,

Michael

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-11 : 11:49:43
This article shows a split fuction that you can use the parse your comma separated values in the row:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-11 : 12:14:43
may be other ways too but following are the two solution coming into my mind
1) you may need a parser (logic inside a function) to parse each value, and devide it into four columns. example

declare @x varchar(100),@itr smallint
declare @tab table (col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10))
set @itr=1
set @x='ABC123,1,Z,some text'
While @itr<5
BEGIN
If @itr=1
INSERT INTO @tab (col1) values (LEFT(@x,Charindex(',',@x)-1))
If @itr=2
update @tab set col2 = LEFT(@x,Charindex(',',@x)-1)
If @itr=3
update @tab set col3 = LEFT(@x,Charindex(',',@x)-1)

If @itr=4
update @tab set col4 = @x

SET @x=Replace(@x,LEFT(@x,Charindex(',',@x)),'')
SEt @itr=@itr+1
END
select * from @tab

2) since you're importing from a lagacy system (in files), why not just read the CSV file straight away using the following sql and dump it into the target table in column format

BULK
INSERT CSVTest -- is the target table name
FROM 'c:\csvtest.txt' -- the file that has the CSV data
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)


Cheers
MIK
Go to Top of Page

michaelhutcheson
Starting Member

2 Posts

Posted - 2013-06-11 : 12:17:13
Thanks Mik,

I wil take a look at your first solution. I was unable to perform the second solution 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 " ".
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-11 : 13:16:14
quote:
Originally posted by michaelhutcheson

Thanks Mik,

I wil take a look at your first solution. I was unable to perform the second solution 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 " ".




I believe you should not be facing any issue using the second method, unless the data have additional comma(s) inside the data unit e.g.
ABC,123,1,Z,SomeText
Or
ABC123,1,Z,Some,Text

As far as the double qoutes or any other (unwanted) character(s), you can remove it simply using the Replace function once the data is imported

Cheers
MIK
Go to Top of Page
   

- Advertisement -