SQL Function to Check UK National Insurance Number

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Vivek Nigam [ADN TECHNOLOGIES LIMITED]

--  Created date: 14-January-2020 @ 09:33 AM

--  Description: Sql Function  to Check UK National Insurance Number 

--- Check UK NationalInsurance If Not Correct Retrun  0

--- File Name - Fn_06_Function_To_Validate_UK_NationalInsuranceNumber_NI.sql

--- fn_ValidateUKNationalInsuranceNumber

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

--  Modify On -               

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

--Check if a function exists in Database Before Creating - If exists Then Drop 

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

BEGIN

DROP FUNCTION [dbo].[fn_ValidateUKNationalInsuranceNumber]

END

GO

CREATE FUNCTION [dbo].[fn_ValidateUKNationalInsuranceNumber] (@NINo VARCHAR(20))

  RETURNS bit

  AS 

  BEGIN

    DECLARE @Result bit

IF UPPER(@NINo) LIKE 

  '[A-CEGHJ-PR-TW-Za-ceghj-pr-tw-z][A-CEGHJ-NPR-TW-Za-ceghj-npr-tw-z][0-9][0-9][0-9][0-9][0-9][0-9]'

      --'[A-CEGHJ-PR-TW-Z][A-CEGHJ-NPR-TW-Z]

      -- [0-9][0-9][0-9][0-9][0-9][0-9][A-D ]'

  OR UPPER(@NINo) LIKE 

  '[A-CEGHJ-PR-TW-Za-ceghj-pr-tw-z][A-CEGHJ-NPR-TW-Za-ceghj-npr-tw-z][0-9][0-9][0-9][0-9][0-9][0-9][A-DFMa-dfm]'

    --AND UPPER(left(@NINo,2)) NOT IN 

    --   ('BG','GB','KN','NK','NT','TN','ZZ')

      -- Valid

      SET @Result = 1

    ELSE

      -- Invalid

      SET @Result = 0

    RETURN @Result

  END