November 9, 2010 at 4:49 am
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 12:53 pm
I don't really get what you are trying to do but..
select top 1 (SUBSTRING(@ADDRESSLINE, nullif(patindex('% '+a.format+' %', ' ' + @addressline + ' '), 0), len(a.pattern)))
FROM tableA a
WHERE f.Country = @COUNTRY
Given that you have two entries in tableA from each country it will get the top 1 (and with no order you don't even know which one you will get). Most likely you will need to pass in the format you want also.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2010 at 1:07 pm
Try a join instead of a string function buried in a UDF.
IF OBJECT_ID(N'tempdb..#Patterns') IS NOT NULL
DROP TABLE #Patterns;
CREATE TABLE #Patterns (
Country CHAR(3),
Pattern VARCHAR(100));
INSERT INTO #Patterns
( Country, Pattern )
VALUES ( 'USA', -- Country - char(3)
'[0-9][0-9][0-9][0-9][0-9]' -- Pattern - varchar(100)
);
INSERT INTO #Patterns
( Country, Pattern )
VALUES ( 'USA', -- Country - char(3)
'[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' -- Pattern - varchar(100)
);
;WITH CTE (Zip, Country) AS
(SELECT '99999','usa' UNION ALL
SELECT '123','usa' UNION ALL
SELECT '12345-1234','usa')
SELECT *
FROM CTE
INNER JOIN #Patterns AS P
ON CTE.Country = P.Country
AND PATINDEX(P.Pattern, CTE.Zip) > 0;
You can easily turn that into an update or select statement on a real table (instead of a CTE).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2010 at 6:27 am
Hello Gsquared,
Thanks a lot thats working.
November 10, 2010 at 1:30 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply