x

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.

more ▼

asked Jun 28, 2011 at 02:31 PM in Default

avatar image

SQLFAQ
11 1 1 4

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.

Jun 28, 2011 at 02:32 PM Oleg
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

Oleg

more ▼

answered Jun 28, 2011 at 02:46 PM

avatar image

Oleg
17.1k 3 7 28

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.

Jun 29, 2011 at 07:54 AM SQLFAQ

@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.

Jun 29, 2011 at 08:18 AM Oleg

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.

Jun 29, 2011 at 08:50 AM SQLFAQ

@SQLFAQ I updated my answer. Please let me know if the function does what you need it to do.

Jun 29, 2011 at 09:37 AM Oleg

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.

Jun 29, 2011 at 01:54 PM SQLFAQ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2018

asked: Jun 28, 2011 at 02:31 PM

Seen: 4121 times

Last Updated: Apr 25, 2012 at 06:42 PM

Copyright 2016 Redgate Software. Privacy Policy