question

SQLFAQ avatar image
SQLFAQ asked

SQL command for the MOD10 Validation

I'm looking SQL command for the MOD10 Validation on 23rd numeric number. We have all 22 numbers are numeric from that I need to get the 23rd number as MOD10 check. Please feel free to share with me if any of you have the logic.
sql-server-2005
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
I have posted a plain T-SQL routine here to do just that few months ago. Let me try to find it and I will post it.
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered

Here it goes:

    -- declare test variables
    declare @cc varchar(23);
    declare @len int;
    
    -- set test variables values
    select @cc = '37347497262302553647921', @len = len(@cc);
    
    if 
    (
        select 
            sum
            (
                case
                    when (@len - record_no) % 2 = 1 then
                        case 
                            when digit * 2 > 9 then digit * 2 - 9
                            else digit * 2
                        end
                    else digit
                end
            ) result
            from 
            (
                select top (@len) 
                    row_number() over (order by [object_id]) record_no, 
                    substring(@cc, row_number() over 
                        (order by [object_id]), 1) digit
                    from sys.objects
            ) t
    ) % 10 = 0 
        select 'valid' 
    else 
        select 'invalid';
    go

Just restate it as you see fit. The number provided in the sample results in the valid combination compliant with CC10 a.k.a. MOD10 algorithm.

In your case, you probably want to consider passing the 22 digits and one more zero on the right of it to the guts of the select statement, which returns the sum. Take 10 - (mod 10 of it) and you will get the value of the rightmost digit as it should be in place of zero.

<\!-- **Begin Edit**

Here is the inline scalar function calculating the value of the control digit. For example, if your final number has to be CC10 compliant 23 digits number and you already know your 22 digits input then pass these 22 digits as a parameter to the function and it will return the value of the control digit. The latter can then be used to append to the right of the original input. You can use less digits if you need to but not more. A good way to check the validity of the function below is you pass any of your existing credit card numbers (except the last digit) as a parameter to it and the function will "reveal" the value of the last digit on your card. Since the function operates with assumption that the input does not include the control number (of course), the logic to pick the numbers to interrogate by the algorithm steps is reversed from the one in the original validation query above, the one which did need the control number to be already included and simply stated whether the whole bunch is compliant or not. Here is the function definition:

    /*
        This function calculates the value of the control number using 
        CC10 a.k.a. MOD10 algorithm. It expects an input of up to 22 
        digits passed into the function as varchar. Since this function 
        calculates control number rather than validating the input, the 
        logic to pick the numbers to interrogate is reversed( digit * 2 
        and if greater than 9 then reduce by 9 else leave digit * 2 alone). 
        In other words, this function expects an input which does not 
        include a control (rightmost) digit, and calculates the value of
        that control digit. Then the calling code can append the returned 
        control digit to the right of the original input thus getting the 
        whole CC10 compliant result. To test this function, just pass the
        digits of any of your credit cards (without the rightmost digit)
        and the function will return the number matching that last digit
        on your card.
    
    */
    
    create function dbo.udf_CalculateMod10ControlNumber (@input varchar(22))
        returns int
    as
    
    begin;
     
        return 
        (
            select (10 -  
                sum
                (
                    case
                        when (len(@input) - record_no) % 2 = 0 then
                            case 
                                when digit * 2 > 9 then digit * 2 - 9
                                else digit * 2
                            end
                        else digit
                    end
                ) % 10) % 10 result
                from 
                (
                    select top (len(@input)) 
                        row_number() over (order by [object_id]) record_no, 
                        substring(@input, row_number() over 
                            (order by [object_id]), 1) digit
                        from sys.objects
                ) t
        );
        
    end;
    go
    -- test. Let's assume that there is an American Express 
    -- card 3788-749649-42835. Passing the first 14 digit to the
    -- function (no spaces or dashes) should return 5 and it does

    select dbo.udf_CalculateMod10ControlNumber('37887496494283');

    result
    -----------
    5

Of course in reality it would be far better to maintain a tiny Tally table instead of interrogating the sys.objects every time the function is called. For example,

    -- create a small table which can be used by the function
    create table dbo.TinyTally(N int not null identity(1, 1) primary key clustered);
    go
    
    -- insert 23 records into it
    insert into dbo.TinyTally default values;
    go 23
    
    -- now the bottom select currently stating
    select top (len(@input)) 
        row_number() over (order by [object_id]) record_no, 
        substring(@input, row_number() over 
            (order by [object_id]), 1) digit
        from sys.objects
        
    -- can be reduced to 
    select top (len(@input)) 
        N record_no, 
        substring(@input, N, 1) digit
        from dbo.TinyTally

**End Edit** -->

<\!-- **Begin Edit 2**

Here is the version which will work for SQL Server 2000. This is reduced to work only with 22 character inputs because I don't remember whether SQL Server 2000 lets you pass a variable into **select top (@n)** or not. If it does though then please replace the **select top 22** part with the original **select top (len(@input))**. Here is the script:

    create function dbo.udf_CalculateMod10ControlNumber2K (@input varchar(22))
        returns int
    as
    
    begin;
    
        return 
        (
            select (10 -  
                sum
                (
                    case
                        when (len(@input) - record_no) % 2 = 0 then
                            case 
                                when digit * 2 > 9 then digit * 2 - 9
                                else digit * 2
                            end
                        else digit
                    end
                ) % 10) % 10 result
                from 
                (
                    select top 22 -- (len(@input))
                        N record_no, substring(@input, N, 1) digit
                        from dbo.TinyTally
                ) t
        );
    
    end;
    go

The above assumes that TinyTally table already exists and has 23 (or more) records in it.

**End Edit 2** -->

Oleg

10 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLFAQ avatar image SQLFAQ commented ·
Thanks for you reply Oleg. I belive my question was not straight. Based on my inpute value 22 digit I need to make 23rd digit as MOD10 check value.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@SQLFAQ I understand. This is why I have a comment on the bottom of my answer explaining how to calculate it. Please let me know whether you need a procedure returning the control number value or a scalar function returning the same and I will add it to my answer.
0 Likes 0 ·
SQLFAQ avatar image SQLFAQ commented ·
I may need to return this result as value and concatenate into other value so I think the scalar funciton will do good for my requirement. Thanks for your quick response.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@SQLFAQ I updated my answer. Please let me know if the function does what you need it to do.
0 Likes 0 ·
SQLFAQ avatar image SQLFAQ commented ·
Thanks for your effort Oleg. I will test this logic and let you know the result some time next week. I hope this script will work fine but still I will confirm the result.
0 Likes 0 ·
SQLFAQ avatar image SQLFAQ commented ·
HI Oleg, Unfortunately I have to use the SQL server 2000. Can able to make this logic for SQL 2000?. Thanks
0 Likes 0 ·
SQLFAQ avatar image SQLFAQ commented ·
Unfortunatley I have to use the SQL server 2000 for my requriement. Can you able to make this logic in SQL server 2000?.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@SQLFAQ I don't recall whether SQL Server 2000 allows **select top (@n)** when **@n** is a variable. You can test it on your end, I don't have SQL Server 2000 at work. If it does then you have no problem at all, all you have to do is to follow what I have on the bottom of my answer (create a tiny tally table and replace the bottom select). In case if the **select top (len(@input))** part still gives you a problem then you can just comment out the **top (len(@input))**. This will reduce the function to work only with 22 character input, but this is what you need anyway. I will update the answer in few minutes.
0 Likes 0 ·
mikezik avatar image mikezik commented ·
Oleg, Thank you for this function. This exactly I was looking for.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
I tried to convert to comment the answer which was meant to be a comment which someone just posted, but now it is completely gone. I don't know why it happened so please accept my apologies.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.