Loop thru a String, Like this code in visual basic
-
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
-
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
-
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
-
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 ?
-
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
-
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) = '')
 
t1WHERE
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] -
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)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply