Comma Seperated Values

Question

Post reply

Comma Seperated Values

Trybbe

Hall of Fame

Points: 3967
More actions
September 2, 2010 at 7:57 am

#228644

Hi
May you please help
how do I insert comma seperated values into a temp table.
eg. i have procedure that takes values 1,2,3,4,5 into parameter @val
I want to insert those values into a temp table and get a result like
1
2
3
4
5
Please help

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Click here for the latest Delimited Split Function.

Then, it's just:

SELECT ITEM

FROM dbo.DelimitedSplit8k(@val, ',')

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

anand_vanam SSCrazy Points: 2376 More actions · Answer 2

DECLARE @FileName VARCHAR(8000)

SET @FileName='SSC1, SSC2, SSC3'

CREATE TABLE #FileNames

(FileID INT IDENTITY(1,1),

FileNameList VARCHAR(30)

)

SET @FileName=LTRIM(RTRIM(@FileName))

--SELECT @FileName

BEGIN

DECLARE @Count int

DECLARE @Cnt int

DECLARE @Len int

set @Cnt=0

if (CHARINDEX(',',@FileName,1)!=0)

BEGIN

WHILE(@Cnt<LEN(@FileName))

BEGIN

SET @COUNT=CHARINDEX(',',@FileName,@Cnt+1)

IF (@Count=0)

SET @Cnt=@Cnt+LEN(@FileName)

ELSE

BEGIN

SET @Len=@Count-@Cnt

--SELECT SUBSTRING(@FileName,@Cnt,@Len)

INSERT INTO #FileNames (FileNameList) Values (SUBSTRING(@FileName,@Cnt,@Len))

SET @Cnt=@Count+1

END

--Select Reverse(Substring(Reverse(@FileName),1,CHARINDEX(',',Reverse(@FileName),1)-1))

INSERT INTO #FileNames (FileNameList) Values (Reverse(Substring(Reverse(@FileName),1,CHARINDEX(',',Reverse(@FileName),1)-1)))

END

ELSE

INSERT INTO #FileNames (FileNameList) Values (@FileName)

END

SELECT * FROM #FileNames

drop table #FileNames

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

WayneS (9/2/2010)
Click here for the latest Delimited Split Function.
Then, it's just:
SELECT ITEM
FROM dbo.DelimitedSplit8k(@val, ',')

Nice resource, Wayne.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95461 More actions · Answer 4

anand_vanam (9/2/2010)
DECLARE @FileName VARCHAR(8000)
SET @FileName='SSC1, SSC2, SSC3'
CREATE TABLE #FileNames
(FileID INT IDENTITY(1,1),
FileNameList VARCHAR(30)
)
SET @FileName=LTRIM(RTRIM(@FileName))
--SELECT @FileName
BEGIN
DECLARE @Count int
DECLARE @Cnt int
DECLARE @Len int
set @Cnt=0
if (CHARINDEX(',',@FileName,1)!=0)
BEGIN
WHILE(@Cnt<LEN(@FileName))
BEGIN
SET @COUNT=CHARINDEX(',',@FileName,@Cnt+1)
IF (@Count=0)
SET @Cnt=@Cnt+LEN(@FileName)
ELSE
BEGIN
SET @Len=@Count-@Cnt
--SELECT SUBSTRING(@FileName,@Cnt,@Len)
INSERT INTO #FileNames (FileNameList) Values (SUBSTRING(@FileName,@Cnt,@Len))
SET @Cnt=@Count+1
END
END
--Select Reverse(Substring(Reverse(@FileName),1,CHARINDEX(',',Reverse(@FileName),1)-1))
INSERT INTO #FileNames (FileNameList) Values (Reverse(Substring(Reverse(@FileName),1,CHARINDEX(',',Reverse(@FileName),1)-1)))
END
ELSE
INSERT INTO #FileNames (FileNameList) Values (@FileName)
END
SELECT * FROM #FileNames
drop table #FileNames

Would you care to do a performance test of this code against the function that I linked to? Let's make it a nice test... say 1 million rows of data. Stuff to be split out can be any length < 8000 characters. Commas should not be in the same position in the rows... they need to be random.

I predict that the function will blow the socks off of this code. Heck, I'd predict that while this code is running, I might have enough time to go get and consume a drink.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 5

CirquedeSQLeil (9/2/2010)
WayneS (9/2/2010)
Click here for the latest Delimited Split Function.
Then, it's just:
SELECT ITEM
FROM dbo.DelimitedSplit8k(@val, ',')
Nice resource, Wayne.

Thanks. I've been using it so much lately, that I've made it into a snippet.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Trybbe Hall of Fame Points: 3967 More actions · Answer 6

Trybbe

Hall of Fame

Points: 3967

September 3, 2010 at 3:26 am

#1216067

Thanks guys but what if I have two variables

WayneS SSC Guru Points: 95461 More actions · Answer 7

Trybbe (9/3/2010)
Thanks guys but what if I have two variables

Just call the DelimitedSplit8K function for each one.

However, I sense that there is something else going on. Do you need to link these variables together? Can you give us the complete story on this?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2