Function for a checksum calculation using MOD 97...

Question

Post reply

Function for a checksum calculation using MOD 97...

James Millar-305032

Ten Centuries

Points: 1047
More actions
October 24, 2013 at 11:13 am

#300452

Hi,
I need to create a function that will provide me with a checksum to verify my data output. For the first 30 alphanumeric characters in each row of my output I need to multiply each number by its position in the row - i.e. a data row would be something like this: 01CHDACH01INGL010004804950002 I then need to multiply each record by its position in the row and add them together like so: 0*1 + 1*2 + 0*3 (alphas are ignored)........ + 2*30.
Once I have the total sum (767) I then need to apply the MOD 97 algorithm to get my row figure (88). This goes on for each row in the file with my end result being the total of all the rows.
Hope that makes sense...
Thanks,
James
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Here you go

create function Mod97Checksum(@input varchar(30))

returns table

as

return

select sum(cast(substring(@input,N,1) as int) * N)%97 as check_sum

from (

select top(datalength(@input)) row_number() over (order by (select null)) as N

from (values(1),(1),(1),(1),(1),(1)) a(x)

,(values(1),(1),(1),(1),(1),(1)) b(x)

) Tally(N)

where substring(@input,N,1) between '0' and '9'

usage:

select My30CharColumn, isnull(check_sum,0) as [CheckSum]

from MyTable

cross apply dbo.Mod97Checksum(My30CharColumn)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 2

Wow that is brilliant thanks MM!

I have never used the APPLY operator before, but it works perfectly. I would love to say that it all makes perfect sense to me but.... Could you simply summarize what is going on with this part of the function?

select top(datalength(@input)) row_number() over (order by (select null)) as N

from (values(1),(1),(1),(1),(1),(1)) a(x), (values(1),(1),(1),(1),(1),(1)) b(x)

) Tally(N)

Thanks again!

James

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

That part of the code is simply building an "on-the-fly" Tally/Numbers table, which will be used to address each character in the input string.

See This Article by Jeff Moden to learn a bit more about the concept of a Tally table.

Performing this on the fly tends to be quicker than using an actual table for this sort of job, surprisingly.

The TOP() operator is there to make sure we get exactly the right amount of numbers to address each character in the string once and no more - as there is no point checking beyond the length of the input.

So, all the code does is generate a list of whole numbers from 1 to X where X is the length of the input.

Those numbers are then used in the substring to pull out each individual character, compare it to make sure it is a digit between 0 and 9 and then it can be converted to a numeric type and multiplied by it's ordinal position.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 4

Thanks, I get the bigger picture now, but what is the "values" part doing? Why the 6 "(1)" records and the significance of the a(x) and b(x)? Sorry for being a dumb-***!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

James Millar-305032 (10/25/2013)
Thanks, I get the bigger picture now, but what is the "values" part doing? Why the 6 "(1)" records and the significance of the a(x) and b(x)? Sorry for being a dumb-***!

VALUES(1),(1),(1),(1),(1),(1)

Is a Table Value Constructor, which was introduced in SQL2008 and is equivalent to this:

SELECT 1 UNION ALL

SELECT 1

...but much easier to write and shorter!

The "a(x) and b(x)" are just table/column aliases given to the inline tables constructed by the VALUES statements.

Imagine you had a table created from the 6 UNIONed SELECTS above, called "SixOnes" for arguments sake....

Then this:

SELECT ROW_NUMBER() OVER(ORDER BY SELECT(NULL)) as N

FROM SixOnes

is equivalent to this

SELECT ROW_NUMBER() OVER(ORDER BY SELECT(NULL)) as N

FROM (VALUES(1),(1),(1),(1),(1),(1)) AS a(x)

I have two sets of VALUES, "a" and "b" so that they CROSS JOIN to produce 36 rows, which is enough to cover your 30 character string.

I could have used one VALUES() statement with 30 numbers from 1 to 30, then I could forego the ROW_NUMBER() function, but I didn't want to type that much....(starting to wish I hadn't been lazy in the first place now :hehe:)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Function for a checksum calculation using MOD 97...

Cookies on SQLServerCentral