SQL FUNCTION HELP

Question

Post reply

SQL FUNCTION HELP

Deepthy

SSCommitted

Points: 1823
More actions
November 9, 2010 at 6:48 am

#388192

Hi all,
I have a postcode Lookup table TableA
DECLARE @TableA
TABLE (
ID [bigint] IDENTITY(1,1) NOT NULL
country VARCHAR(50) NOT NULL
pattern varchar(100)
format varchar(100)
);
INSERT @TableA
(country,pattern,format)
VALUES ('United Kingdom','X9 9XX','[A-Z][0-9] [0-9]),
('United Kingdom','X99 9XX','[A-Z][0-9][0-9] [0-9][A-Z][A-Z]'),
('United States','99999','[0-9][0-9][0-9][0-9][0-9]')
('United States','99999-9999','[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]');
and I have a function sample
create FUNCTION [dbo].[sample](@Country NVARCHAR(100),@Addressline NVARCHAR(100))
RETURNS nVARCHAR(100)
AS
BEGIN
DECLARE @RESULT nVARCHAR(100)
set @RESULT= ( select top 1 (SUBSTRING(@ADDRESSLINE, nullif(patindex('% '+a.format+' %', ' ' + @addressline + ' '), 0), len(a.pattern)))
FROM tableA a
WHERE f.Country = @COUNTRY )
RETURN @result
end
and I execute
UPDATE cleaned_addresslines
SET CLEAN_addressline = sample(country,addressline) where addressline is not null
the function is updating with clean_addressline only with the format 'X9 9XX' if the country is UK
and similarly only updating clean_addressline with the fomat 99999 if the country is united states.
How can I alter the function to also look for all the formats for each country based on the primary key ID.
Any help is really appreciated.
Thnaks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 1

sumit.agrawal

SSCommitted

Points: 1616

November 9, 2010 at 8:07 am

#1248320

U can use case... inside function....

Jeff Moden SSC Guru Points: 1004499 More actions · Answer 2

@Deepthy,

Are you all set, now?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Deepthy SSCommitted Points: 1823 More actions · Answer 3

@jeff

yes I am all set now with the help of Gsquared post in sql server newbies - 'sql query help' topic.

I used a join on the lookup table instead of a UDF to generate the @result

Thank you very much.