Loop thru a String, Like this code in visual basic

Question

Post reply

Loop thru a String, Like this code in visual basic

Digs

SSCertifiable

Points: 7333
More actions
March 26, 2005 at 1:00 pm

#163997

Hi,
Looping thru strings I found useful in vb 6.0, so how would one replicate this in TSQL
I have used Cusors to loop thru a Rows in a table, which is sort of the same thing , but I just wanted to know how to do this in TSQL
Visual Basic example
Dim Found as Integer
Dim Results as String
Dim Str as String
Str = "Blue,Red,Green,Yellow,END"
Do until Len(Str) < 5
     Found = instr(1,Str,",",1)
     Result = mid(str,1,Found-1)

     Debug.print Result
     Str = mid(str,Found+1)
Loop

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 1

Check the BOL for string functions

Here you go:

declare

@Found Integer,

@Results varchar(100),

@sStr varchar(100)

set @sStr = 'Blue,Red,Green,Yellow,END'

while Len(@sStr) > 4

begin

set @Found = patindex('%,%',@sstr)

set @Results = left(@sstr,@Found-1)

print isnull(@Results,'<null>')

set @sStr = substring(@sstr,@Found+1,len(@sstr)-@found)

end

Tejal Sumeet Deshpande SSC-Addicted Points: 428 More actions · Answer 2

Hi,

Following script will give you same result as above VB code,without using cursor.

Declare @Found varchar(100),

@Results varchar(100),

@STR varchar(100),

@length int,

@leng int

set @STR = 'Blue,Red,Green,Yellow,END'

set @length=len(@str)

set @leng=Patindex('%,%',@str)

while(@leng<>0)

begin

set @Found=@str

set @STR=substring(@str,@leng+1,@length)

set @results=replace(@found,','+@str,'')

set @leng=Patindex('%,%',@str)

set @length=len(@str)

print @results

end

Regards,

Tejal

Digs SSCertifiable Points: 7333 More actions · Answer 3

Awesome, I never expected such a complete answer !

The reason for my asking for this code is because I need to tag a material description.

For example

SKU, Description

1234567, "Blue,Green, Red,Pink"

7895431, "Blue,Black, Red,Gold"

7531591, "Blue,Green, Red,Yellow"

2589631, "Blue,Green, Black,red"

7891354, "Blue,Green, Red,white"

I need to search thur the description and see if these words exist ( Black or Gold ) and tag it as "Y", if not there tag it as "N".

I used Vb6.0 code above to do this. So if you have any advice on how to do this correctly, please advise ?

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 4

You'd set up a many-to-many table that relates the SKU (item) table to the color table.

Item_SKU -- Item_Colors -- Colors

Then to find the color, you'd write a query to get Item join Item_Colors join Colors.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

You certainly don't need to loop through the string to search for some pattern. Just use CHARINDEX or PATINDEX. They return a value > 0 when a match is found. However, I'm with Russell that you should rather store each possible colour for a SKU in a table than build a list of colours and store them all in just one column.

And, just for giggles, here's how an "extraction query" might look like in T-SQL:

DECLARE @strComma VARCHAR(1000)

SET @strComma = 'Blue,Red,Green,Yellow'

SELECT the_value

FROM

(

SELECT

CAST(RIGHT(LEFT(@strComma,Number-1)

, CHARINDEX(',',REVERSE(LEFT(','+@strComma,Number-1)))) AS CHAR(30)) the_value

FROM

master..spt_values

WHERE

Type = 'P' AND Number BETWEEN 1 AND LEN(@strComma)+1

AND

(SUBSTRING(@strComma,Number,1) = ',' OR SUBSTRING(@strComma,Number,1) = '')

&nbsp t1

WHERE

the_value = 'Blue'

OR

the_value = 'Yellow'

the_value

------------------------------

Blue

Yellow

(2 row(s) affected)

Joe made a good point in utilizing a numeric helper table. I've used SQL Server's internal spt_values. In production you would rather consider building your own table.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

We do not use Oracle contenization characters is MS SQL Server and I'm pretty sure "For 1" just isn't going to hack it either. Joe, do you have a solution that will work in MS SQL Server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)