SQL FUNCTION TO VALIDATE UK POST CODE

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Created date: 20-May-2016 @ 09:23 AM

-- Description: Sql Function  to validate UK POST Code.

-- ISDATEFORMATCorrect returns 1 when the input expression evaluates to a valid PostCode ; otherwise it returns 0.

-- Modified By  :  Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Modify On -        

-- Input - Pass The PostCode You want to Validate 

-- OutPut - 

-- Return - 1 = Valid

---Return - 0 - Invalid    

-- SQL Script File Name - scr_05_Function_To_Validate_Date_Format_In_Staging.sql

-- This Function will be Find Non-Ascii Characters in One Varchar Column or Mulitiple Columns using SQL Server

-- This Function Will Creatd / excuated by Batch File Called - 01_Create_All_Functions_To_Validate_Data_After_Importing_Into_Staging.bat

-- Here is a UDF I built to detectc columns with extended ascii charaters. It is quick and you can 

-- extended the character set you want to check. The second parameter allows you to switch between checking anything outside the standard character set or allowing an extended set:

--USAGE:

--select Address1 

--from PropertyFile_English

--where fn_ContainsNonPrintableChars(Address1, 1) = 1

--File Name - scr_05_Function_To_Validate_UK_PostCode_Format_In_Staging.sql

--Modify By 

--Modifyed Date - 

--How To Test / USe..

--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('Test SK13 8LY') --In-Valid

--INSERT @TestTab VALUES('XA1 1AA') --In-Valid

--INSERT @TestTab VALUES('AAA 1AA') --In-Valid

--INSERT @TestTab VALUES('RH16 1BQ') --Valid

--INSERT @TestTab VALUES('RH16 1BQZZZZZ')  --In-Valid

--INSERT @TestTab VALUES('RH16 1BQZZ')  --In-Valid

--INSERT @TestTab VALUES('ZZZ')  --In-Valid 

--INSERT @TestTab VALUES('CR02EN')  --Valid

--INSERT @TestTab VALUES('CR0 2EN')  --Valid

--INSERT @TestTab VALUES('CR0 2ENZZ')  --In-Valid

--SELECT  PostCode,CASE WHEN dbo.fn_ValidatePostCodeUK(PostCode) IS NULL THEN '0' 

--                    ELSE 1 END FROM   @TestTab

----As you can see, the query uses T-SQL's PATINDEX() function. This function searches a string for a 

----sub-string that matches a wildcard pattern. It returns the starting position of the pattern within the string, 

----or zero if the pattern is not found. In this case, we call PATINDEX() six times per row, 

----once for each of the possible postcode patterns.

-- A more refined regex, which excludes all invalid areas and some invalid districts is:

-- (GIR 0AA)|

--(((A[BL]|B[ABDFHLNRSTX]?|

--C[ABFHMORTVW]|D[ADEGHLNTY]|

--E[HNX]?|F[KY]|G[LUY]?|

--H[ADGPRSUX]|

--I[GMPV]|

--JE|

--K[ATWY]|

--L[ADELNSU]?|

--M[EKL]?|

--N[EGNPRW]?|

--O[LX]|

--P[AEHLOR]|

--R[GHM]|

--S[AEGKLMNOPRSTY]?|

--T[ADFNQRSW]|

--UB|

--W[ADFNRSV]|

--YO|

--ZE)[1-9]?[0-9]|((E|N|NW|SE|SW|W)1|EC[1-4]|WC[12])[A-HJKMNPR-Y]|(SW|W)([2-9]|[1-9][0-9])|EC[1-9][0-9]) [0-9][ABD-HJLNP-UW-Z]{2})

----/^(?:GIR 0AA|(?:(?:(?:A[BL]|B[ABDHLNRSTX]?|C[ABFHMORTVW]|D[ADEGHLNTY]|E[HNX]?|F[KY]|G[LUY]?|H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGK-PRSTY]?|T[ADFNQRSW]|UB|W[ADFNRSV]|YO|ZE)[1-9]?\d|(?:(?:E|N|NW|SE|SW|W)1|EC[1-4]|WC[12])[A-HJKMNPR-Y]|(?:SW|W)(?:[2-9]|[1-9]\d)|EC[1-9]\d)\d[ABD-HJLNP-UW-Z]{2}))$/i

   -- =============================================

--Check if a function exists on a ADN-TECHNOLOGIES-LIMITED-STAGING Database Before Creating - If exists Then Drop 

  IF object_id('dbo.fn_ValidatePostCodeUK') IS NOT NULL

BEGIN

DROP FUNCTION [dbo].fn_ValidatePostCodeUK

END

GO

 CREATE FUNCTION dbo.fn_ValidatePostCodeUK

(

@PostCode VARCHAR(100)

)

RETURNS VARCHAR(8)

AS

BEGIN             

       IF @PostCode = '' RETURN 'Valid' -- Asked By Paul to Check If @PostCode Have  Null String Then Return As Valid Post Code  - Check If @PostCode Have Null String 

   IF @PostCode IS  NULL RETURN 'Valid' -- Asked By Paul to Check If @PostCode Have  Null String Then Return As Valid Post Code - Check If @PostCode Have Null Value 

    IF LEN(@PostCode) < 3 RETURN NULL -- Add to Fix The Issue If The Post Code Is Only 2 Char. - Is INVALID

       

SET @PostCode=UPPER(Replace(@PostCode,' ',''))

SET @PostCode= LEFT(@PostCode,LEN(@PostCode)-3)+' '+ Right(@PostCode,3)

        RETURN        

              

COALESCE(

                           -- AANN NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 8),

                           -- AANA NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 8),

                           -- ANN NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 7),

                           -- AAN NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 7),

                           -- ANA NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 7),

                           --   AN NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 6),

                           --   Special case GIR 0AA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% GIR 0AA %', ' ' + @PostCode + ' '), 0), 7)

                )

       

END

-- RETURNS BIT

--AS

--BEGIN

--                    --SET @PostCode = IIF(ISNULL(dbo.fn_ExtractUKPostCode(@PostCode),'999'),dbo.fn_ExtractUKPostCode(@PostCode))

-- SET @sPostCode=UPPER(Replace(@sPostCode,' ',''))

-- SET @sPostCode= LEFT(@sPostCode,LEN(@sPostCode)-3)+' '+ Right(@sPostCode,3)

-- --declare @TestTab Table (postcode varchar(50) not null)

-- --Insert @TestTab values(@PostCode) --Valid

--   SET @sPostCode = CASE WHEN dbo.fn_ExtractUKPostCode(@sPostCode) IS NULL THEN '999' 

--                    ELSE dbo.fn_ExtractUKPostCode(@sPostCode) 

--    END

--      --SELECT  @PostCode = CASE 

--     --               WHEN dbo.fn_ExtractUKPostCode(PostCode) IS NULL THEN '999' 

--   --                 ELSE dbo.fn_ExtractUKPostCode(PostCode) END FROM   @TestTab

  

-- RETURN CASE

--      --            WHEN (PATINDEX('999',@sPostCode) = 0  AND 

-- -- PATINDEX('[A-Z][0-9] [0-9][A-Z][A-Z]', @sPostCode) = 0 AND  

-- --PATINDEX('[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]', @sPostCode) = 0 

-- -- ) THEN 0

--                     -- AANN NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     ---- AANA NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     -- ANN NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     -- AAN NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     -- ANA NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     --   AN NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     -- Not a valid postcode

-- --   Special case GIR 0AA

--                     WHEN @sPostCode LIKE 'GIR 0AA' THEN 1

--                     -- Current postcode prefixes

--                     WHEN   LEFT(@sPostCode, 2) NOT IN ('AB', 'AL', 'BA', 'BB', 'BD', 'BH', 'BL', 'BN', 'BR', 'BS', 'BT', 'CA', 'CB', 'CF', 'CH', 'CM', 'CO', 'CR', 'CT', 'CV', 'CW', 'DA', 'DD', 'DE', 'DG', 'DH', 'DL', 'DN', 'DT', 'DY', 'EC', 'EH', 'EN', 'EX', 'FK', 'FY', 'GL', 'GU', 'GY', 'HA', 'HD', 'HG', 'HP', 'HR', 'HS', 'HU', 'HX', 'IG', 'IM', 'IP', 'IV', 'JE', 'KA', 'KT', 'KW', 'KY', 'L', 'LA', 'LD', 'LE', 'LL', 'LN', 'LS', 'LU', 'ME', 'MK', 'ML', 'NE', 'NG', 'NN', 'NP', 'NR', 'NW', 'OL', 'OX', 'PA', 'PE', 'PH', 'PL', 'PO', 'PR', 'RG', 'RH', 'RM', 'SA', 'SE', 'SG', 'SK', 'SL', 'SM', 'SN', 'SO', 'SP', 'SR', 'SS', 'ST', 'SW', 'SY', 'TA', 'TD', 'TF', 'TN', 'TQ', 'TR', 'TS', 'TW', 'UB', 'WA', 'WC', 'WD', 'WF', 'WN', 'WR', 'WS', 'WV', 'YO', 'ZE') 

--                           OR LEFT(@sPostCode, 1) NOT IN ('B', 'E', 'G', 'L', 'N', 'S', 'W') THEN 0

 

       

--                     ELSE 0

--                END

--END