Solved

How do I create a loop in SQL?

Posted on 2012-04-10
12
337 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
[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: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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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
 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL GROUP BY 6 82
Syntax error creating JSON recordset 4 28
StoredProcedure to JSON query faulty syntax 2 29
T-SQL: I need to add an index on a field 5 27
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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