Insert a comma delimited string

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

General SQL Server Forums

New to SQL Server Programming

Insert a comma delimited string

Author

Topic

wilshaw01
Starting Member

4 Posts

Posted - 2013-11-22 : 15:12:04

Hi - I have a problem in a stored procedure trying to insert a comma delimited list into rows.

The parameters for example would be somthing like....

@GarageID int = 20,
@Cars nVarChar(200) = Ford~Fiesta,BMW~320,Volvo~340,Jaguar~XJS

I need to split where the comma is for each new row, and separate each value from either side of the '~' like so....

GarageID    Make      Model
--------------------------------
    20        Ford       Fiesta
    20        BMW        320
    20        Volvo      340
    20        Jaguar     XJS

Any help or point in the right direction would be appreciated.

Wilshaw

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-22 : 15:27:57

You can use a string splitter function - see here for a good one: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Using the delimitedSplit8K function from that article:

DECLARE @GarageID int = 20
declare @Cars nVarChar(200) = 'Ford~Fiesta,BMW~320,Volvo~340,Jaguar~XJS';
SELECT @GarageID, LEFT(Item,CHARINDEX('~',Item)-1),STUFF(Item,1,CHARINDEX('~',Item),'') 
FROM dbo.DelimitedSplit8K(@Cars,',')

wilshaw01
Starting Member

4 Posts

Posted - 2013-11-22 : 15:52:44

quote:
Originally posted by James K

You can use a string splitter function - see here for a good one: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Using the delimitedSplit8K function from that article:
DECLARE @GarageID int = 20
declare @Cars nVarChar(200) = 'Ford~Fiesta,BMW~320,Volvo~340,Jaguar~XJS';
SELECT @GarageID, LEFT(Item,CHARINDEX('~',Item)-1),STUFF(Item,1,CHARINDEX('~',Item),'')
FROM dbo.DelimitedSplit8K(@Cars,',')

Thanks - exactly what I was looking for.

Wilshaw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources