Solved

sql loop help

Posted on 2011-03-16
3
299 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
[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
3 Comments
 
LVL 41

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:
Ephraim Wangoya 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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