November 9, 2010 at 6:48 am
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
November 9, 2010 at 8:07 am
U can use case... inside function....
November 16, 2010 at 12:45 am
Are you all set, now?
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply