Could i Put "While" loop into "Where"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Could i Put "While" loop into "Where"

Author

Topic

Cheni
Starting Member

2 Posts

Posted - 2007-01-08 : 03:07:44

hi all,
i am trying to write a sql function to receive an address such as (7, Oakleigh Close) as a parameter to search the corresponding postcode. Just wonder how can i make it works?

ALTER FUNCTION [dbo].[GetPostCode](@Address nvarchar(200))
RETURNS @Result TABLE
(
Address varchar(100) NULL,
StartNo float NULL,
EndNo float NULL
)
AS
BEGIN

DECLARE @LEN INT, @CURRENT NVARCHAR(50), @STREETNO NVARCHAR(50), @TEMP NVARCHAR(10)

SET @LEN = 0
SET @TEMP = ''
SET @CURRENT = ''
SET @STREETNO = ''

INSERT @Result
SELECT * FROM [PostCode]
WHERE CHARINDEX([Address],@Address)>0 OR
[STARTNO] >= (
WHILE @LEN <= LEN(@ADDRESS)
BEGIN
SET @TEMP = SUBSTRING(@ADDRESS, @LEN, 1)
IF @TEMP LIKE '[0-9]'
BEGIN SET @STREETNO = @STREETNO + @TEMP
SET @LEN = @LEN + 1
END
IF SUBSTRING (@ADDRESS, @LEN +1,1) NOT LIKE '[0-9]'
BEGIN SELECT CONVERT(INT, @STREETNO) END
END
SET @LEN = @LEN + 1 )
RETURN;

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-08 : 08:32:58

Your best bet, if looping like that is really necessary, is to put that logic in a user defined function. The syntax you are trying to write is not valid, as you have discovered.

Alternatively, you can find the first non-numeric character in your string, and take everything to the left of that, and use that as your Street No with a simple expression like this:


declare @TestValue varchar(100);
set @TestValue = '125 High Street'

select left(' ' + @TestValue, PatIndex('%[^0-9]%',@TestValue)) as StreetNo

That will return the StreetNo, which you can convert to an integer if you like. Note that ' ' will be returned if there is no numeric characters found in the beginning of the string.

Use this code as a guideline, read up on PatIndex() to see how it works, test different values with it to make sure that it works for all of your cases, and good luck.

- Jeff

Cheni
Starting Member

2 Posts

Posted - 2007-01-08 : 20:26:24

Thanks

Peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources