?
Solved

loop through a 2 comma delimited string SQL SERVER

Posted on 2005-03-20
7
Medium Priority
?
986 Views
Last Modified: 2012-06-22
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
Comment
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
7 Comments
 
LVL 14

Accepted Solution

by:
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

by:Thandava Vallepalli
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

by:vinny45
ID: 13589299
wow thats some crazy code. is it possible to do this without using temp tablesj or cursor? if not itsvtk wins
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Expert Comment

by:Thandava Vallepalli
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

by:Hilaire
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')

-- 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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
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

by:Racim BOUDJAKDJI
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

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.

Join & Ask a Question