• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

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?
0
Michael Sterling
Asked:
Michael Sterling
  • 8
  • 3
1 Solution
 
Lee SavidgeCommented:
Using a while loop and charindex. Give me some examples of input and expected output.
0
 
nishant joshiTechnology Development ConsultantCommented:
below is a simple example of while loop

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO

Open in new window


you can use cursor also for looping more data and you can also use cte for looping data through recursion.

Happy Quering...:)
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@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'.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@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?
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
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?
0
 
nishant joshiTechnology Development ConsultantCommented:
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.

;with testcte(ID)
as
(select 1
union all
select ID+1 from testcte where ID < 5
)
select * from testcte

Open in new window


as above example will give a result of 1 to 4 and stop for 5.

thanks
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@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?
0
 
nishant joshiTechnology Development ConsultantCommented:
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
      IF  CHARINDEX(@string, 'A') > 0
          SET @result= @result + 'Have'
      ELSE IF  CHARINDEX(@string, 'S') > 0
            SET @result= @result + ' Fun'
       SELECT @result
      SET @pos = @pos + 1
      SET @string = SUBSTRING(@string, @pos, @len)
END

Open in new window

0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@nishantcomp2512: this didn't return anything when I tried it. Did it for you? It have me 2 iterations, but both were blank. returned '',...
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@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 '',..."
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@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
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
your help was inspirational enough to deserve credit. thanks for your time and effort
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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