Solved

sql loop help

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What Is an Error? 2 59
Need to replicate a Log table 4 43
2008 to 2016 jump (SQL Server) 1 33
Help with SQL pivot 11 50
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
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…

710 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