Solved

sql loop help

Posted on 2011-03-16
3
290 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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 …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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