loop through a 2 comma delimited string SQL SERVER

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
vinny45Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Thandava VallepalliCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thandava VallepalliCommented:
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
vinny45Author Commented:
wow thats some crazy code. is it possible to do this without using temp tablesj or cursor? if not itsvtk wins
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Thandava VallepalliCommented:
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
HilaireCommented:
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')

-- your to update your table
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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.