SQL FUNCTION TO VALIDATE POST CODE USING GRIDALL FILE

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Created date: 04-August-2016 @ 14:51 PM

-- Description: Sql Function  to validate POST CODE USING Gridall file.

----latest Gridall file have been downloaded , there are total 25,829,71  post code records. 

--Data has been migrated in staging table. Scribe DTS has been  created to import the  file in staging table (Table name - scr_z_UK_PostCode_Validation_Table_Using_Gridall).

-- ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.

-- ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). 

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

-- Modify On -        

-- Input - Pass The POST CODE Value Which You want to Validate 

-- OutPut - 

-- Return - 0 = Valid

---Return - 1 - Invalid    

-- SQL Script File Name - scr_24_Function_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging.sql

-- This Function will be created / excecated by Batch File Called - 03_Create_All_Functions_To_Validate_Data_After_Importing_Into_Staging.bat

-- Table name - scr_z_UK_PostCode_Validation_Table_Using_Gridall

-- To Test - Select dbo.fn_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging('KT13 0RH') FROM  scr_z_UK_PostCode_Validation_Table_Using_Gridall AS PO

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

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

  IF object_id('dbo.fn_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging', N'FN') IS NOT NULL

BEGIN

DROP FUNCTION [dbo].fn_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging

END

GO

  CREATE FUNCTION dbo.fn_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging

(

@vPostCode VARCHAR(100)

)

RETURNS INT

AS

-- Returns the 0 If Post Code Is Valid .  1 - If Post Code Is InValid.

BEGIN  

DECLARE @vRET INT;  

IF @vPostCode = '' RETURN 0 -- Check If @PostCode Have  Null String Then Return As Valid Post Code  - Check If @PostCode Have Null String 

IF @vPostCode IS  NULL RETURN 0 -- Check If @PostCode Have  Null String Then Return As Valid Post Code - Check If @PostCode Have Null Value 

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

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

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

SELECT @vRET = COUNT(*)   

FROM scr_z_UK_PostCode_Validation_Table_Using_Gridall PO  

WHERE PO.PostCode_2 = @vPostCode; 

--WHERE PO.PostCode_2 = @vPostCode; 

RETURN IIF(@vRET >= 1,0,1);  

END;  

GO