Solved

sql loop help

Posted on 2011-03-16
3
285 Views
Last Modified: 2012-05-11

I have a variable that has an address. State and zip have been pulled out and the state is stored in a variable called @state
 
@address   may have this in it  5220 Main St Winston Salem..

I have a function that gets the word in a string.. Its called udf.getword and it just needs @address and a number that tells me what word you want to pull out from the left.. so

select udf.getword (@address,1) gives me Salem.. and (@address,2) gives me Winston..etc..


How do get below using my udf?
I want to loop 4 times to get the below results populated in a variable called @lastSearchValue.

First loop gives me Main St Winston Salem
Second loop gives me St Winston Salem
Third loop gives me Winston Salem
Fourth loop gives me Salem
0
Comment
Question by:cheryl9063
3 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35151859
When you loop four times, you get four different results. Do you want all those results in one variable? Do you always want to loop it four times or its just an example?
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 35151944
I want it to loop 4 times and populate 4 different variables

@loop1
@loop2
@loop3
@loop4
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35152858
try
declare @I integer
declare @temp varchar(max), @var1 varchar(max), @var2 varchar(max), @var3 varchar(max), @var4 varchar(max)
declare @address varchar(max)

set @I = 1

while @I < 5 
begin
  select @temp = udf.getword(@address, @I)
  if @I = 1 
      set @var1 = @temp
  else if @I = 2 
      set @var2 = @temp
  else if @I = 3 
      set @var3 = @temp
  else if @I = 4 
      set @var4 = @temp       
  
  set @I = @I + 1 
end

Open in new window

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

'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 …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

862 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

21 Experts available now in Live!

Get 1:1 Help Now