October 24, 2013 at 11:13 am
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
October 24, 2013 at 4:46 pm
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);October 25, 2013 at 8:13 am
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
October 25, 2013 at 8:23 am
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);October 25, 2013 at 9:02 am
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-***!
October 25, 2013 at 12:07 pm
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 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
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);Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply