Extract UK postcode

Wed Aug 13, 2008 by Peter Larsson in sql-server-2008, algorithms, sql-server-2005, sql-server-2000

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

Here are some sample data to play with

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