Inserting comma seperated values into multiple col

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 2005 Forums

Transact-SQL (2005)

Inserting comma seperated values into multiple col

Author

Topic

AKP2008
Starting Member

45 Posts

Posted - 2009-08-26 : 02:35:42

Hi,

How can i insert comma seperated values into multiple columns.

My table structure is like this

DECLARE @Test TABLE
(
EmpId INT IDENTITY(1,1),
EmpFirstName VARCHAR(100),
EmpLastName VARCHAR(100),
Addr1 VARCHAR(100),
City VARCHAR(100),
STATE VARCHAR(100),
Zip VARCHAR(10)
)

My input string is 'FName1,Lname,Address1,Address2,city,state,zip|FName1,Lname,Address1,Address2,city,state,zip'

First i have to split using '|' and then insert into my table.

Please help.

Thanks in Advance.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-26 : 02:41:09

try like this
declare @str1 varchar(max)
set @str1= 'M001111,M001222,M001333'

SELECT
replace(SUBSTRING(@str1,charindex(',',@str1,v.number),abs(charindex(',',@str1,charindex(',',@str1,v.number)+1)-charindex(',',@str1,v.number))),',','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(',' + @str1, v.number, 1) = ','
or
use fnParseList(',', Col7) function for this
search this function in this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting,delimited,lists

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-26 : 02:43:25

Hi Try this once

Declare @Str1 VARCHAR(100)
Select @Str1 = 'Sh,Li,wi'

;WITH csvtbl(i, j)
AS
(
SELECT 0, j = CHARINDEX(',', @Str1+',')
UNION ALL
SELECT CAST(j + 1 AS INT), j = CHARINDEX(',', @Str1+',', j + 1)
FROM csvtbl
WHERE CHARINDEX(',', @Str1+',', j + 1) <> 0
)

SELECT SUBSTRING(@Str1+',', C.i, C.j-i) from csvtbl c

AKP2008
Starting Member

45 Posts

Posted - 2009-08-26 : 05:36:14

Hi,

I want result like this.

col1 col2 col3
Sh Li wi

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-26 : 06:04:09

quote:
Originally posted by AKP2008

Hi,

I want result like this.

col1 col2 col3
Sh Li wi

Try this

set @str='''sh'',''li'',''wi'''
exec ('insert into Test values('+@str+')')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources