Split the string and insert into table

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)

Split the string and insert into table

Author

Topic

reogeo
Starting Member

2 Posts

Posted - 2012-07-03 : 07:56:55

Hi all,
This is the format of the string i am passing to the stored procedure and i want to split the string with ; as the delimiter and insert it into a table
String Format ;Alfa Romeo;Opel;Suzuki;Tiger
the table format after insertion is asbelow
ID UserID Make
1 2 Alfa Romeo

2 2 Opel

3 2 Suzuki

4 2 Tiger

Can i do the spliting and insertion in the same stored procedure...Please help me out in this issue..
Thanks in Advance..

rocknpop
Posting Yak Master

201 Posts

Posted - 2012-07-03 : 11:30:16

try this:

DECLARE @str VARCHAR(100) = ';Alfa Romeo;Opel;Suzuki;Tiger', @xml xml
SET @str = SUBSTRING(@str,2,LEN(@str))
SET @str = '<root><row>' + REPLACE(@str,';','</row><row>') + '</row></root>'
SET @xml = CAST(@str AS XML)
SELECT tab.col.value ('.','varchar(50)')
FROM @xml.nodes('/root/row') tab(col)

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources