Extract UK postcode - SQL Server Forums

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

General SQL Server Forums

Script Library

Extract UK postcode

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 08:31:17

[code]CREATE FUNCTION dbo.fnExtractPostCodeUK
(
@Data VARCHAR(8000)
)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN COALESCE(
-- AANN NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),
-- AANA NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),
-- ANN NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),
-- AAN NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),
-- ANA NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),
-- AN NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 6),
-- Special case GIR 0AA
SUBSTRING(@Data, NULLIF(PATINDEX('% GIR 0AA %', ' ' + @Data + ' '), 0), 7)
)
END[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 03:09:51

[code]declare @TestTab Table (postcode varchar(50) not null)

Insert @TestTab values('SK13 8LY') --Valid
Insert @TestTab values('M1 1AA') --Valid
Insert @TestTab values('M60 1NW') --Valid
Insert @TestTab values('GIR 0AA') --Valid
Insert @TestTab values('CR2 6XH') --Valid
Insert @TestTab values('DN55 1PT') --Valid
Insert @TestTab values('W1A 1HQ') --Valid
Insert @TestTab values('EC1A 1BB') --Valid
Insert @TestTab values('India') --Invalid
Insert @TestTab values('12345') --Invalid
Insert @TestTab values('Glossop SK13 8LY') --Invalid but contains a valid postcode
INSERT @TestTab VALUES('XA1 1AA') --WHAT WILL THE FUNCTION SAY ABOUT THIS??? WAS JUST A PHAT-PHINGER on "X"
INSERT @TestTab VALUES('AAA 1AA') --OR HOW ABOUT THIS???

SELECT PostCode,
dbo.fnExtractPostCodeUK(PostCode)
FROM @TestTab[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources