Procedure to validate an SA identity number

  • 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

    Attachments:
    You must be logged in to view attached files.
  • frdrckmitchell7 wrote:

    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?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

  • sorry the requirement was an attachment and I was ask to paste thr attachment info rather which I have now done.

    thanks

  • frdrckmitchell7 wrote:

    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?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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 @b-2 = @b-2 * 2

    SET @C = 0

    WHILE (@B > 0)

    BEGIN

    SET @C = @C + @b-2 % 10

    SET @b-2 = @b-2 / 10

    END --WHILE

    SET @C = @C + @a

    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