Hi Guys
I am trying to create a procedure that will validate a South African ID Number (I think in the US this would equate to a SSN) based on the below reasons and I need help from you clever guys please as I am completely stuck.
The procedure must evaluate to the id number entered. This is the table creation with the record entered:
CREATE TABLE Clientdataset (
Idnumber VARCHAR(13) DEFAULT NULL,
FirstName varchar(50) DEFAULT NULL,
Surname varchar(50) DEFAULT NULL,
DateOfBirth date DEFAULT NULL,
FolderNumber varchar(20) DEFAULT NULL,
Gender VARCHAR(9) NULL
CHECK(Gender IN ('Male', 'Female', 'Unknown')),
RecordNumber int NOT NULL IDENTITY(1,1)
PRIMARY KEY,
) ON [PRIMARY]
INSERT INTO Clientdataset(Idnumber,FirstName,Surname,DateOfBirth,
FolderNumber,Gender)
VALUES('6809115290084', 'Robert', 'Sidney', '1968-09-11', 'RS01','Male')
SELECT * FROM Clientdataset
The explanation w.r.t. validation of ID number is as follows:
The number is a numeric field 13 digits long in the form YYMMDDGxxxNRC Where:
The first 6 digits shall conform to the date of birth of the subject, in the form YYMMDD where YY = Year in numbers with century removed, MM = Month in numbers, DD day of month.
G indicates Gender, where < 5 indicates female and >=5 indicates male
xxx is a sequence number in the range of 001 to 999.
N is an indication of citizenship where 0 indicates South African, and 1 indicates Foreign.
R indicates Race, now no longer used and will generally be 8
C is the Mod10 check digit
The Mod10 check digit (ISO 2894/ANSI 4.13) is checked in the following manner:
****THIS IS WHAT THE PROCEDURE MUST CHECK TO VALIDATE THE ID NUMBER ENTERED*****
The LUHN formula applies some simple arithmetic to a number to calculate a digit that must agree with the check digit, the last digit that appears on the number. Here are the formula's three steps:
1. Beginning with the second digit from the end (on the right), take every other digit and multiply it by two.
2. Proceeding right to left, take each of the digits skipped in step 1 and add them to the result digits from step 1. If the result of doubling a number in step 1 resulted in a two-digit number (such as 7 + 7 = 14), use each of these digits (1 and 4) in adding the digits in step 2.
3. Subtract the result obtained in step 2 from the next higher number that ends in 0. The result must agree with the check digit.
For this explanation I am going to use ID number 860506 5 397 08 3
a) Add all the digits of the ID number in the odd positions (except for the last number, which is the control digit):
8+0+0+5+9+0 = 22
b) Take all the even digits as one number and multiply that by 2:
656378 * 2 = 1312756
c) Add the digits of this number together (in b)
1+3+1+2+7+5+6 = 25
d) Add the answer of C to the answer of A
22+25 = 47
e) Subtract the second character from D from 10, this number should now equal the control character
10-7 = 3 = control character (3)
Is there perhaps someone who knows how to write a procedure like this? Your help is greatly appreciated.
Thanks for your help!
Kind regards
Hi Guys
I need to write a procedure to validate an SA identity number (a SSN in the west). Please look at the attached doc for a detailed explanation.
Thank you and kind regards
That's your requirement, but what is your question?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 29, 2021 at 5:46 pm
BTW - most people will not open an attachment in Word or Excel format due to the risk associated with these types of documents. It would be much better to post what is in the document instead of attaching the document.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 29, 2021 at 6:00 pm
sorry the requirement was an attachment and I was ask to paste thr attachment info rather which I have now done.
thanks
January 29, 2021 at 6:06 pm
sorry the requirement was an attachment and I was ask to paste thr attachment info rather which I have now done.
thanks
Is there a specific question there, or are you asking us to write the entire solution for you?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 29, 2021 at 7:31 pm
Hi Phil
The following proc only gets me the first two steps of the algorithm. I'm stuck after that.
CREATE PROCEDURE [dbo].[ValidateSAIDNumber]
@IDNumber VARCHAR (13)
AS
BEGIN
DECLARE @a INT -- ~Holds Negative Index numbers
DECLARE @b-2 INT -- ~Holds Positive Index numbers
DECLARE @C INT
DECLARE @D INT -- ~The check digit
DECLARE @I INT -- ~Counter Variable
SET @D = -1
SET @a = 0
SET @b-2 = 0
BEGIN TRY
PRINT 'Getting Negative Numbers____________________'
SET @I = 0
WHILE (@I < 6)
BEGIN
PRINT SUBSTRING(@IDNumber, 2 * @I + 1, 1)
SET @a = @a + CAST(SUBSTRING(@IDNumber, 2 * @I + 1, 1) AS INT)
SET @I = @I + 1
END --WHILE
PRINT 'Getting Positive Numbers____________________'
SET @I = 1
WHILE (@I < 7)
BEGIN
PRINT SUBSTRING(@IDNumber, 2 * @I + 1, 1)
SET @b-2 = @b-2 * 10 + CAST(SUBSTRING(@IDNumber, 2 * @I, 1) AS INT)
SET @I = @I + 1
END --WHILE
SET @C = 0
WHILE (@B > 0)
BEGIN
END --WHILE
SET @D = 10 - (@C % 10)
IF (@D = 10)
SET @D = 0
IF (CAST(SUBSTRING(@IDNumber, 13, 1) AS INT) = @D)
RETURN 1
ELSE
RETURN 0
END TRY
BEGIN CATCH
RETURN 0
END CATCH
RETURN 0
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply