How do I create a loop in SQL?
I need to create a stored procedure that takes in a string and it's length and parses that string for certain letters, builds a string, based on the letters it finds in that string and returns that newly built string. I know I'll need a loop in that procedure somewhere, at least i'm pretty sure i'll need a loop. Is there an example of this some where that I can find or does someone have an idea of how i would create this procedure in SQL?
Lee
Using a while loop and charindex. Give me some examples of input and expected output.
below is a simple example of while loop
you can use cursor also for looping more data and you can also use cte for looping data through recursion.
Happy Quering...:)
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GOyou can use cursor also for looping more data and you can also use cte for looping data through recursion.
Happy Quering...:)
ASKER
@lsavidge: for example if I get the string 'AS'. 'A', for example would mean "Have" and 'S' would equal 'Fun' so I would want the return string to be "Have Fun'.
ASKER
@nishantcomp2512: I've never used a cursor before or a cte for recursion (recursive loops) is there a simple example of this I can follow, or can you give me a simple example?
ASKER
below is an example of what i've attempted so far. I get the word 'Have' but I keep getting a NULL for the 'S'
DECLARE @string NVARCHAR
DECLARE @len INT = 2
DECLARE @pos INT = 1
DECLARE @result NVARCHAR = ''
DECLARE @temp NVARCHAR = ''
SELECT @string = 'AS'
WHILE @len <> 0
BEGIN
SET @len = @len - 1
SELECT
CASE WHEN
CHARINDEX(@string, 'A') > 0
THEN
@result + 'Have'
WHEN
CHARINDEX(@string, 'S') > 0
THEN
@result + ' Fun'
END
SET @pos = @pos + 1
SET @string = SUBSTRING(@string, @pos, @len)
END
any ideas what i'm doin wrong here?
DECLARE @string NVARCHAR
DECLARE @len INT = 2
DECLARE @pos INT = 1
DECLARE @result NVARCHAR = ''
DECLARE @temp NVARCHAR = ''
SELECT @string = 'AS'
WHILE @len <> 0
BEGIN
SET @len = @len - 1
SELECT
CASE WHEN
CHARINDEX(@string, 'A') > 0
THEN
@result + 'Have'
WHEN
CHARINDEX(@string, 'S') > 0
THEN
@result + ' Fun'
END
SET @pos = @pos + 1
SET @string = SUBSTRING(@string, @pos, @len)
END
any ideas what i'm doin wrong here?
example of cte
http://msdn.microsoft.com/en-us/library/ms186243.aspx
There are too many examples you will find after googling...but i am giving simple one as below.
as above example will give a result of 1 to 4 and stop for 5.
thanks
http://msdn.microsoft.com/en-us/library/ms186243.aspx
There are too many examples you will find after googling...but i am giving simple one as below.
;with testcte(ID)
as
(select 1
union all
select ID+1 from testcte where ID < 5
)
select * from testcteas above example will give a result of 1 to 4 and stop for 5.
thanks
ASKER
@nishantcomp2512: i see what you're saying so i guess i would just need to figure out how to parse my string into a cte. maybe that's what I need an example of how to do?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@nishantcomp2512: this didn't return anything when I tried it. Did it for you? It have me 2 iterations, but both were blank. returned '',...
ASKER
@nishantcomp2512: sorry for the typo above, it should've read:
"this didn't return anything when I tried it. Did it for you? It gave me 2 iterations, but both were blank. returned '',..."
"this didn't return anything when I tried it. Did it for you? It gave me 2 iterations, but both were blank. returned '',..."
ASKER
@nishantcomp2512: below is code that i've tried. if you look at the results you can see that after the first iteration (pass), because of this line:
"SET @string = right(@string, LEN(@string) -1)"
the new string is 'S'. which is evidenced by the output,...yet, on the second iteration (2nd pass), it still falls into the first 'IF' statement as if the letter 'A' is still part of the string. I'm not sure why it's doing this. Any ideas?
DECLARE @string NVARCHAR(21)
DECLARE @len INT = 2
DECLARE @pos INT = 0
DECLARE @result NVARCHAR(1000) = ''
DECLARE @temp NVARCHAR = ''
SELECT @string = 'AS'
WHILE @len <> 0
BEGIN
SELECT @string AS 'STARTING STRING'
SET @len = @len - 1
IF CHARINDEX(@string, 'A') >= 0
BEGIN
SET @result= @result + 'Have'
SELECT @result AS '1ST RSLT'
SET @string = right(@string, LEN(@string) -1)
SELECT @string 'NEW STRNG'
END
ELSE IF CHARINDEX(@string, 'S') >= 0
BEGIN
SET @result= @result + 'Fun'
SELECT @result 'NXT RSLT'
SET @string = right(@string, LEN(@string) -1)
SELECT @string '3RD STRNG'
END
--ELSE IF CHARINDEX(@string, 'S') >= 0
-- SET @result= @result + ' Fun'
-- SELECT @result as 'the string'
--SET @string = SUBSTRING(@string, 1, 0)
END
"SET @string = right(@string, LEN(@string) -1)"
the new string is 'S'. which is evidenced by the output,...yet, on the second iteration (2nd pass), it still falls into the first 'IF' statement as if the letter 'A' is still part of the string. I'm not sure why it's doing this. Any ideas?
DECLARE @string NVARCHAR(21)
DECLARE @len INT = 2
DECLARE @pos INT = 0
DECLARE @result NVARCHAR(1000) = ''
DECLARE @temp NVARCHAR = ''
SELECT @string = 'AS'
WHILE @len <> 0
BEGIN
SELECT @string AS 'STARTING STRING'
SET @len = @len - 1
IF CHARINDEX(@string, 'A') >= 0
BEGIN
SET @result= @result + 'Have'
SELECT @result AS '1ST RSLT'
SET @string = right(@string, LEN(@string) -1)
SELECT @string 'NEW STRNG'
END
ELSE IF CHARINDEX(@string, 'S') >= 0
BEGIN
SET @result= @result + 'Fun'
SELECT @result 'NXT RSLT'
SET @string = right(@string, LEN(@string) -1)
SELECT @string '3RD STRNG'
END
--ELSE IF CHARINDEX(@string, 'S') >= 0
-- SET @result= @result + ' Fun'
-- SELECT @result as 'the string'
--SET @string = SUBSTRING(@string, 1, 0)
END
ASKER
your help was inspirational enough to deserve credit. thanks for your time and effort