Solved

# loop through a 2 comma delimited string SQL SERVER

Posted on 2005-03-20
Medium Priority
986 Views
I have 2 comma-delimited strings like "2,2,4,5" AND "1,5,3,6"

These two will always have the same amount of values.

id like to loop through these like this in T-SQL MetaCode:

@FirstArray =  "2,2,4,5"
@SecondArray=  "1,5,3,6"

count = (numOfValues in FirstArray) NOTE: only need to evaluate one array because both will always have the same amount of values.

while (x = 1, x =< count )

UPDATE aTable SET field1=@FirstArray{x} WHERE field2 = secondArray{x}

loop

oh ya, this needs to work with double or triple digit numbers if that makes sense
0
Question by:vinny45
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 14

Accepted Solution

Thandava Vallepalli earned 1000 total points
ID: 13588668
Create this function .........

CREATE  FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

--http://www.winnetmag.com/SQLServer/Articles/ArticleID/21071/pg/1/1.html

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you canâ€™t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

==================================

Run this update...

UPDATE aTable SET field1= T1.VALUE
FROM (SELECT VALUE FROM fn_Split('2,2,4,5', ',') ) T1,
(SELECT VALUE FROM fn_Split('1,5,3,6', ',') ) T2
WHERE FIELD2 = T2.VALUE AND T1.IDX = T2.IDX
==================================

itsvtk
0

LVL 14

Expert Comment

ID: 13588683
For ur information......  after creating the above function........

select *
from fn_Split('1,5,3,6', ',')

Will return below format

idx    value
-----  ------
1        1
2        5
3        3
4        6

itsvtk
0

Author Comment

ID: 13589299
wow thats some crazy code. is it possible to do this without using temp tablesj or cursor? if not itsvtk wins
0

LVL 14

Expert Comment

ID: 13589385
Hi,

I think no way other than the above...   Here is the microsoft artical on your type of requirements.........

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/treatyourselfListing_01.txt

itsvtk
0

LVL 26

Expert Comment

ID: 13589393
You might also give this one a try ...
Uses basically the same method than itsvtk's, just another flavour

CREATE FUNCTION dbo.ufnParse2Arrays(@strArray1 varchar(8000), @strArray2 varchar(8000))
RETURNS
@t_Items table(Array1_Item int, Array2_Item int)
AS
BEGIN
DECLARE @strItem1 varchar(12),  @strItem2 varchar(12), @sep1Pos int, @sep2Pos int
select @strArray1 = coalesce(@strArray1 + ',', ''), @strArray2 = coalesce(@strArray2 + ',', '')
select @sep1Pos = charindex(',', @strArray1), @sep2Pos = charindex(',', @strArray2)
WHILE @sep1Pos > 0  BEGIN
select @strItem1 = LEFT(@strArray1, @sep1Pos - 1), @strItem2 = LEFT(@strArray2, @sep2Pos - 1)
INSERT INTO @t_Items SELECT CAST(@strItem1 AS int), CAST(@strItem2 AS int)
where isnumeric(@strItem1)=1 and isnumeric(@strItem2)=1
select @strArray1 = RIGHT(@strArray1, DATALENGTH(@strArray1) - @sep1Pos),
@strArray2 = RIGHT(@strArray2, DATALENGTH(@strArray2) - @sep2Pos),
@sep1Pos = charindex(',', @strArray1),
@sep2Pos = charindex(',', @strArray2)
END
RETURN
END
go

-- how to use it
select * from dbo.ufnParse2Arrays('2,2,4,5', '1,5,3,6')

UPDATE A
SET field1 = B.Array1Item
from aTable A
INNER JOIN dbo.ufnParse2Arrays('2,2,4,5', '1,5,3,6') B on A.field2 = B.Array2Item

Hilaire

0

LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 1000 total points
ID: 13589565
<<is it possible to do this without using temp tablesj or cursor?>>
No need to use cursors for that... This should answer your question ...faster...

create function fn_split(@input_string varchar(1000), @delimiter_used varchar(1))
returns @table1 table
(idx int, code varchar(50))
as
begin
declare @var1 int, @scanned_string varchar(1), @current_string varchar(1000), @var_idx int
declare @table_var table (idx_temp int, code varchar(50))
set @var1 = 1
set @var_idx = 1

set @current_string = rtrim(@input_string)

while @var1 <  len(@current_string)
begin
set @scanned_string = substring(@current_string, @var1, 1)
if ascii(@scanned_string) = ascii(@delimiter_used)
begin
insert into @table_var(idx_temp, code)
select @var_idx ,left(@current_string, @var1 - 1)
set @current_string = right(rtrim(@current_string), len(rtrim(@current_string)) - @var1)
set @var1 = 0
set @var_idx = @var_idx + 1
end

set @var1 = @var1 + 1
end
insert into @table_var(idx_temp, code)
select @var_idx, rtrim(@current_string)
insert into @table1(idx, code) select idx_temp, code from @table_var
return
end

--Example of usage
update aTable set field1 = (select code from fn_split('4,6,7,9', ',') where idx = 1 ) where field2 = (select code from fn_split('4,6,8,9', ',') where idx = 1)

0

LVL 23

Expert Comment

ID: 13589603
You may also create an sp to deal whith the complete process...

create update_sp (@string1 varchar(1000), @string2 varchar(1000), @delimiter varchar(1),@index_value int)
as
update aTable set field1 = (select code from fn_split(@string1, @delimiter) where idx = @index_value  ) where field2 = (select code from fn_split(@string2, @delimiter) where idx = @index_value )

usage...

exec update_sp '5,6,7,8', '7,8,9,3', ',', 1
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down â€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
###### Suggested Courses
Course of the Month9 days, 6 hours left to enroll

#### 764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.