Solved

How do I create a loop in SQL?

Posted on 2012-04-10
12
335 Views
Last Modified: 2012-04-11
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
Comment
Question by:mikesExpertExchange
  • 8
  • 3
12 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37829507
Using a while loop and charindex. Give me some examples of input and expected output.
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37829511
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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 37829538
@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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 37829551
@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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 37829689
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
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37829710
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 37829739
@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
 
LVL 14

Accepted Solution

by:
nishant joshi earned 500 total points
ID: 37830942
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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 37833144
@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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 37833200
@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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 37833635
@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
 
LVL 1

Author Closing Comment

by:mikesExpertExchange
ID: 37834897
your help was inspirational enough to deserve credit. thanks for your time and effort
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now