?
Solved

How do I create a loop in SQL?

Posted on 2012-04-10
12
Medium Priority
?
340 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:Michael Sterling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Michael Sterling
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 1

Author Comment

by:Michael Sterling
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:Michael Sterling
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
 
LVL 1

Author Comment

by:Michael Sterling
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 2000 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:Michael Sterling
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:Michael Sterling
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:Michael Sterling
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:Michael Sterling
ID: 37834897
your help was inspirational enough to deserve credit. thanks for your time and effort
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

771 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