I’ve been working on NHS number validation recently so thought I’d share my code. Although there is guidance available on how to validate NHS numbers using modulus 11 check digit valiation, no one seems to have put up any code for it. It’s simple but hopefully it’ll be useful.
It will obviously not tell you whether a particular number has been assigned to a patient. It will only tell you if the number has a valid check digit. It is still a useful tool for checking large volumes of records for transcription errors and the like.
I’ve succesfully used it in three different languages: T-SQL, Excel VBA and R. In all three cases it is implemented as a user defined function which will return either 1 or 0 (1 = valid, 0 = invalid).
T-SQL
CREATE FUNCTION [dbo].[fnNHSvalidation] (@NHSnumber NVARCHAR(10))
RETURNS INTEGER
AS
BEGIN
/*Declare variables to hold values for calculation and output*/
DECLARE @ReturnValue INTEGER
DECLARE @Modulus INTEGER
DECLARE @UniformNumberCheck NVARCHAR(1)
DECLARE @NHSlength INTEGER
/*Find the length of the supplied NHS number*/
SET @NHSlength = LEN(@NHSnumber)
/*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 = SubString(@NHSnumber,1,1)
SET @B = SubString(@NHSnumber,2,1)
SET @C = SubString(@NHSnumber,3,1)
SET @D = SubString(@NHSnumber,4,1)
SET @E = SubString(@NHSnumber,5,1)
SET @F = SubString(@NHSnumber,6,1)
SET @G = SubString(@NHSnumber,7,1)
SET @H = SubString(@NHSnumber,8,1)
SET @I = SubString(@NHSnumber,9,1)
SET @J = SubString(@NHSnumber,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 @ReturnValue
END
GO
Excel VBA
Function NHSvalidation(NHSnumber)
Dim Modulus As Integer
Dim UniformNumberCheck As Integer
Dim NHSlength As Integer
NHSlength = Len(NHSnumber)
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim G As Integer
Dim H As Integer
Dim I As Integer
Dim J As Integer
A = CInt(Mid(NHSnumber, 1, 1))
B = CInt(Mid(NHSnumber, 2, 1))
C = CInt(Mid(NHSnumber, 3, 1))
D = CInt(Mid(NHSnumber, 4, 1))
E = CInt(Mid(NHSnumber, 5, 1))
F = CInt(Mid(NHSnumber, 6, 1))
G = CInt(Mid(NHSnumber, 7, 1))
H = CInt(Mid(NHSnumber, 8, 1))
I = CInt(Mid(NHSnumber, 9, 1))
J = CInt(Mid(NHSnumber, 10, 1))
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)) Then
UniformNumberCheck = 1
Else
UniformNumberCheck = 0
End If
Modulus = ((A * 10) + (B * 9) + (C * 8 ) + (D * 7) + (E * 6) + (F * 5) + (G * 4) + (H * 3) + (I * 2))
Modulus = (11 - (Modulus Mod 11))
If (Modulus = J And UniformNumberCheck <> 1 And NHSlength = 10) Or (Modulus = 11 And J = 0 And UniformNumberCheck <> 1 And NHSlength = 10) Then
NHSvalidation = 1
Else
NHSvalidation = 0
End If
End Function
R
NHSvalidation <- function(NHSnumber){
NHSlength<-length(NHSnumber)
A<-as.numeric(substr(NHSnumber,1,1))
B<-as.numeric(substr(NHSnumber,2,2))
C<-as.numeric(substr(NHSnumber,3,3))
D<-as.numeric(substr(NHSnumber,4,4))
E<-as.numeric(substr(NHSnumber,5,5))
F<-as.numeric(substr(NHSnumber,6,6))
G<-as.numeric(substr(NHSnumber,7,7))
H<-as.numeric(substr(NHSnumber,8,8))
I<-as.numeric(substr(NHSnumber,9,9))
J<-as.numeric(substr(NHSnumber,10,10))
if ((A==B)&(B==C)&(C==D)&(D==E)&(E==F)&(F==G)&(G==H)&(H==I)&(I==J))
{UniformNumberCheck<-1}
else
{UniformNumberCheck<-0}
Modulus<-((A*10)+(B*9)+(C*8)+(D*7)+(E*6)+(F*5)+(G*4)+(H*3)+(I*2))
Modulus<-(11-(Modulus%%11))
if (
((Modulus==J) & (UniformNumberCheck!=1) & (NHSlength==10))|((Modulus==11) & (J==0) & (UniformNumberCheck!=1) & (NHSlength==10)))
{ReturnValue<-1}
else
{ReturnValue<-0}
return(ReturnValue)
}
Thank you so much for the NHS number check – it makes me feel the world is such a nice place when a stranger is kind enough to share some code.
You have saved me a huge amount of time and the NHS some money by sharing your code.
Thanks, what a fantastic time-saver!
Just a small typo on the VBA code
“If (Modulus = J And UniformNumberCheck 1 And NHSlength = 10) Or (Modulus = 11 And J = 0 And UniformNumberCheck 1 And NHSlength = 10) Then”
should be:
–> UniformNumberCheck != 1
(or ” = 0″ would work of course).
Thanks again.
Thanks Matthew, it should have been <> but WordPress seems to interpret these as HTML tags, I’ve put the unicode characters in and it seems to be working now. Glad it’s been of use to you!
Pingback: Validate UK NHS Number in SharePoint | Lars Nielsen's Discoveries
This is such a useful post – I was setting up validation for NHS Numbers in SharePoint and I used your Excel formula to test my code against a large number of test cases, to make sure it gives the same result as your formula. If anyone else is interested I’ve posted how to validate NHS Numbers in in SharePoint on my blog here: http://discoverlars.wordpress.com/2014/02/21/validate-uk-nhs-number-in-sharepoint.
Reblogged this on CorkeTech and commented:
If you’re like me and work with NHS numbers regularly, then you need to be aware that the NHS number has a built in check digit. To protect against errors I recommed that you use this function to check the validity of the NHS numbers in your database.
Function ValidNHS(vNHS As Variant) As Boolean
Dim t As Integer
Dim i As Integer
For i = 1 To Len(CStr(iNHS)) – 1
t = t + (Mid(vNHS, i + 1, 1) * (11 – i))
Next i
ValidNHS = (11 – (t Mod 11) = Right(vNHS, 1))
Exit Function
I wanted to do this in SQL, and found a couple of bugs/typos in your code.
I also made a better way of checking whether all numbers were the same. Here’s mine:
CREATE FUNCTION [dbo].[NHS_Number_Validation] (@NHSnumber NVARCHAR(10))
RETURNS BIT
AS
BEGIN
/*Declare variables to hold values for calculation and output*/
DECLARE @ReturnValue BIT
DECLARE @Modulus INTEGER
DECLARE @UniformNumberCheck BIT
DECLARE @NHSlength INTEGER
/*Find the length of the supplied NHS number*/
SET @NHSlength = LEN(@NHSnumber)
/*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 = SubString(@NHSnumber,1,1)
SET @B = SubString(@NHSnumber,2,1)
SET @C = SubString(@NHSnumber,3,1)
SET @D = SubString(@NHSnumber,4,1)
SET @E = SubString(@NHSnumber,5,1)
SET @F = SubString(@NHSnumber,6,1)
SET @G = SubString(@NHSnumber,7,1)
SET @H = SubString(@NHSnumber,8,1)
SET @I = SubString(@NHSnumber,9,1)
SET @J = SubString(@NHSnumber,10,1)
/*Step 1: Ensure NHS number digits are not all equal*/
SELECT @UniformNumberCheck = CASE WHEN LEN(REPLACE(@NHSnumber, LEFT(@NHSnumber,1),”)) = 0
THEN 1 ELSE 0 END
/*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 = 0 AND @NHSlength=10) OR (@Modulus = 11 AND @J = 0 AND @UniformNumberCheck = 0 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 @ReturnValue
END