SQL FUNCTION TO VALIDATE NHS NUMBER FORMAT

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Vivek Nigam (ADN TECHNOLOGIES LIMITED)

-- Created date: 11-May-2016 @ 15:00 PM

-- Description: Sql Function  to validate NHS NUMBER FORMAT.

-- SQL FUNCTION TO VALIDATE National Health Service (NHS) in England NUMBER FORMAT - 

-- IsNHSNumberFormatCorrect Correct returns 1 when the input expression evaluates to a valid NHS NUMBER type; otherwise it returns 0. - National Health Service (NHS) in England Is 10 Dig.

-- Modified By  :  Vivek Nigam (ADN TECHNOLOGIES LIMITED)

-- Modify On -     15/07/2016   

-- Input - Pass The NHS Number You want to Validate 

-- OutPut - 

-- Return - 1 = Valid

---Return - 0 - Invalid    

-- SQL Script File Name - scr_01_Function_To_Validate_NHS_NUMBER_Format.sql

-- This Function will be created / excecated by Batch File Called -

 01_Create_All_Functions_To_Validate_Data_After_Importing_Into_Staging.bat

-- Domain rules: NHS Numbers are 10 digits long. 

-- They consist solely of the numerals 0 through 9. 

-- The digits are numbered from left to right. 

---NHS Number Validation - NHS Number Checker

 --The format for the new NHS Number adopts ten digits. The final digit is used as a check to validate the first nine digits. Validation is achieved using the Modulus 11 algorithm. 

 --Four steps are involved in applying the Modulus 11 algorithm to calculate the last digit. 

 --These are:

 --STEP 1 - Starting from the left, multiply each of the first nine digits by (11 - digit position). 

 --For example contents of digit position 1 would be multiplied by 10, contents of digit position 2 by 9, contents of digit position 3 by 8, etc.

 --STEP 2 - Add the results of the nine multiplications together.

 --STEP 3 - Divide the total by 11 and obtain the remainder.

 --STEP 4 - Subtract the remainder from 11 to get the calculated check digit. If the calculated check digit is 11, substitute a value of 0. If the calculated check digit is 10, then the first nine digits are invalid and are not to be used.

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

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

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

BEGIN

DROP FUNCTION [dbo].fn_IsNHSNumberFormatCorrect

END

GO

  CREATE FUNCTION dbo.fn_IsNHSNumberFormatCorrect

(

@vNHSNumber VARCHAR(80)

)

RETURNS BIT

AS

BEGIN

/*Declare variables to hold values for calculation and output*/

DECLARE @ReturnValue INTEGER

DECLARE @Modulus INTEGER

DECLARE @UniformNumberCheck NVARCHAR(1)

DECLARE @NHSlength INTEGER

--- First Check If The Passed NHS Number Is 10 Digit Number - Any NHS Number Which is Less Than 10 Digit should be in-valid by default.. No Check for Moduler11

IF   LEN(@vNHSNumber) <> 10

BEGIN

SET @ReturnValue = 1

RETURN @ReturnValue

END

ELSE IF   NOT (PATINDEX('%[^0-9-]%',  @vNHSNumber) = 0

AND CHARINDEX('-', @vNHSNumber) <= 1

AND @vNHSNumber NOT IN ('.', '-', '+', '^')

AND LEN(@vNHSNumber)> 0

AND @vNHSNumber NOT LIKE '%-%')

BEGIN

SET @ReturnValue = 1

RETURN @ReturnValue

END

ELSE

/*Find the length of the supplied NHS number*/

SET @NHSlength = LEN(@vNHSNumber)

/*Declare variables to hold each digit of the NHS number, note that J is the check digit*/

DECLARE @A INTEGER

DECLARE @B INTEGER

DECLARE @C INTEGER

DECLARE @D INTEGER

DECLARE @E INTEGER

DECLARE @F INTEGER

DECLARE @G INTEGER

DECLARE @H INTEGER

DECLARE @I INTEGER

DECLARE @J INTEGER

/*Set variables to respective digits*/

SET @A = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,1,1))) 

SET @B = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,2,1)))

SET @C = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,3,1)))

SET @D = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,4,1)))

SET @E = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,5,1)))

SET @F = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,6,1)))

SET @G = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,7,1)))

SET @H = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,8,1)))

SET @I = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,9,1)))

SET @J = CONVERT(INT, CONVERT(VARCHAR(1), SubString(@vNHSNumber,10,1)))

/*Step 1: Ensure NHS number digits are not all equal (there is probably a FAR more elegant way of doing this)*/

IF ((@A = @B) AND (@B = @C) AND (@C = @D) AND (@D = @E) AND (@E = @F) AND (@F = @G) AND (@G = @H) AND (@H = @I) AND (@I = @J))

SET @UniformNumberCheck = 1

ELSE

SET @UniformNumberCheck = 0

/*Step 2: Calculate modulus 11*/

SET @Modulus = ((@A * 10.0)+(@B * 9.0)+(@C * 8.0)+ (@D * 7.0)+(@E * 6.0)+(@F * 5.0)+(@G * 4.0)+(@H * 3.0)+(@I * 2.0))

SET @Modulus = 11-(@Modulus % 11)

/*Step 3: Logical statement to establish if NHS number is valid based on results of previous steps*/

IF (@Modulus= @J AND @UniformNumberCheck <> 1 AND @NHSlength = 10) OR (@Modulus = 11 AND @J = 0 AND @UniformNumberCheck <> 1 AND @NHSlength = 10)

SET @ReturnValue = 1

ELSE 

SET @ReturnValue = 0

/*Generate output: 1 indicates a valid NHS number, 0 indicates an invalid NHS number*/

         

RETURN IIF (@ReturnValue = 1, 0, 1)

--RETURN @ReturnValue

END

GO