Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 992
  • Last Modified:

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
0
vinny45
Asked:
vinny45
2 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now