Solved

How do I create a loop in SQL?

Posted on 2012-04-10
12
334 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

760 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

19 Experts available now in Live!

Get 1:1 Help Now