Comma deliminated string - SQL Server Forums

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)

Comma deliminated string

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2010-10-15 : 15:42:15

Been trying to get this to work but no luck so here I am.

I have three parameters I'm passing in.

@param1 = 3
@param2 = 4
@param3 = '1,2,4,5'

I added values for simplicity.

Now, I need four rows in a table, call it MyTable.

So, the four rows would look like this:

col1 col2 col3
3 4 1
3 4 2
3 4 4
3 4 5

Any suggestions on how to achieve this?

Thanks,

Zath

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-10-15 : 16:02:17

Just do a google search on split comma seperated string t-sql.

You'll need to create a function, or just loop through the delimiters. There will be a ton of articles on how to achieve.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-15 : 19:48:33

Are you trying to insert 4 rows or select 4 rows?

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-10-16 : 01:18:11

I had created a function that that detects up to three spaces or comas .
It takes 2 arguements - string, int
1 for the first gap or coma
2 for the second gap or coma
3 for the third gap or coma

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[fn_InstrComaSpace](@text varchar(25),@gaps tinyint)
RETURNS TINYINT
AS
BEGIN
DECLARE @firstGap TINYINT
DECLARE @add TINYINT
DECLARE @total TINYINT
DECLARE @nextGap TINYINT
DECLARE @noOfGaps TINYINT
DECLARE @returnValue TINYINT
SET @returnValue = 0
SET @noOfGaps = 0
SET @add = 0
SET @total = 0
SET @nextGap = 0
set @text = ltrim(rtrim(@text))

WHILE(@add <= LEN(@text))
BEGIN --while

IF(LEN(@text) > 0 ) and (substring(@text, @add,(@add-@total+1))=''
or substring(@text, @add,(@add-@total+1))=',')
BEGIN --IF OUTER

IF(@gaps = 1)
BEGIN -- 1
If(@noOfGaps = 1)
Begin
set @firstGap = @add
set @returnValue = @firstGap
End

END --1

IF(@gaps = 2)
BEGIN -- 2
If(@noOfGaps = 2)
Begin
set @nextGap = @add
set @returnValue = @nextGap
End

END -- 2

IF(@gaps = 3)
BEGIN -- 3
If(@noOfGaps = 3)
Begin
set @nextGap = @add
set @returnValue = @nextGap
End

END --3

set @noOfGaps = @noOfGaps + 1

END -- IF OUTER

SET @add = @add + 1 -- while loop inc
SET @total = @add

END -- WHILE

RETURN @returnValue

END -- function
------- SQL

declare @param1 int
declare @param2 int
declare @param3 varchar(12)
set @param1 = 3
set @param2 =4
set @param3 ='1,2,4,5' -- HAS 3 COMAS IN ALL

DECLARE @MyTable TABLE
(
COL1 INT,
COL2 INT,
COL3 CHAR(1)
)

insert into @MyTable
select @param1,@param2,substring(@param3,0,2) union all
select @param1,@param2,dbo.fn_InstrComaSpace(@param3,1)union all
select @param1,@param2,dbo.fn_InstrComaSpace(@param3,2) union all
select @param1,@param2, cast(substring( @param3,dbo.fn_InstrComaSpace(@param3,3)+1,
len(@param3) - dbo.fn_InstrComaSpace(@param3,3)+1)as int)

select * from @MyTable

-----------------
RESULT

(4 row(s) affected)
COL1 COL2 COL3
----------- ----------- ----
3 4 1
3 4 2
3 4 4
3 4 5

(4 row(s) affected)

ALTHOUGH IT IS NOT ALL THAT CRASH HOT, IT MIGHT SUIT YOUR REQUIREMENT.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-16 : 06:54:41

you should check out how to use a tally table.

You can split the string with only one index seek in one pass with a tally table

Here's a good article. You have to register to the site but it's free.
http://www.sqlservercentral.com/articles/T-SQL/62867/

or do a search for tally table here and start reading.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources