SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Luhn algorithm
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Mike27
Starting Member

USA
6 Posts

Posted - 05/31/2012 :  17:34:53  Show Profile  Reply with Quote
Looking for a SQL solution for the LUHN_A algorithm, which is the alphanumeric version... any good solutions out there that can be shared? Thanks in advance....
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 05/31/2012 :  19:05:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You do the same thing, except that A (ascii value 65) is treated as position value 10, B is treated as 11 and so on...



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Mike27
Starting Member

USA
6 Posts

Posted - 06/01/2012 :  12:27:56  Show Profile  Reply with Quote
So the portion of the code that states
IF @Luhn LIKE '%[^0-9]%'
RETURN @Luhn

is modified to deal with letters A-Z, what does the syntax look like? Thanks.

Mike Marshall
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 06/01/2012 :  13:26:49  Show Profile  Visit robvolk's Homepage  Reply with Quote
IF @Luhn LIKE '%[^0-9A-Z]%'
RETURN @Luhn
Go to Top of Page

Mike27
Starting Member

USA
6 Posts

Posted - 06/01/2012 :  14:04:11  Show Profile  Reply with Quote
Seems like a conversion problem: Conversion failed when converting the varchar value 'A' to data type tinyint...

Mike Marshall
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 06/01/2012 :  14:13:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
You'd have to map alpha characters to their values as Peter mentioned, something like:
SELECT @Plus = @Multiplier * CAST(CASE 
WHEN SUBSTRING(@Luhn, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@Luhn, @Index, 1) 
WHEN SUBSTRING(@Luhn, @Index, 1) LIKE '[A-Z]' THEN ASCII(SUBSTRING(@Luhn, @Index, 1))-55 
ELSE 0 END AS TINYINT),
I'm not sure that will work, or if that creates the correct check digit in the algorithm, you'd have to test it.
Go to Top of Page

Mike27
Starting Member

USA
6 Posts

Posted - 06/01/2012 :  15:23:02  Show Profile  Reply with Quote
Thanks for the conversion tip. Here is the verbage around the checkdigit logic, and how it is a modified Luhn algorithm:

To handle alphanumeric digits (numbers and letters), we actually use the ASCII value (the computer's internal code) for each character and subtract 48 to derive the "digit" used in the Luhn algorithm. We subtract 48 because the characters "0" through "9" are assigned values 48 to 57 in the ASCII table. Subtracting 48 lets the characters "0" to "9" assume the values 0 to 9 we'd expect. The letters "A" through "Z" are values 65 to 90 in the ASCII table (and become values 17 to 42 in our algorithm after subtracting 48).
For the second-to-last (2nd from the right) character and every other (even-positioned) character moving to the left, we just add 'ASCII value - 48' to the running total. Non-numeric characters will contribute values >10, but these digits are not added together; rather, the value 'ASCII value - 48' (even if over 10) is added to the running total. For example, '"M"' is ASCII 77. Since '77 - 48 = 29', we add 29 to the running total — not '2 + 9 = 11'.

For the rightmost character and every other (odd-positioned) character moving to the left, we use the formula '2 * n - 9 x INT(n/5)' (where INT() rounds off to the next lowest whole number) to calculate the contribution of every other character. If you use this formula on the numbers 0 to 9, you will see that it's the same as doubling the value and then adding the resulting digits together (e.g., using 8: '2 x 8 = 16' and '1 + 6 = 7'. Using the formula: '2 x 8 - 9 x INT(8/5) = 16 - 9 x 1 = 16 - 9 = 7') — identical to the Luhn algorithm. But using this formula allows us to handle non-numeric characters as well by simply plugging 'ASCII value - 48' into the formula. For example, '"Z"' is ASCII 90. '90 - 48 = 42' and '2 x 42 - 9 x INT(42/5) = 84 - 9 x 8 = 84 - 72 = 12'. So we add 12 (not '1 + 2 = 3') to the running total.
Go to Top of Page

Mike27
Starting Member

USA
6 Posts

Posted - 06/03/2012 :  12:37:08  Show Profile  Reply with Quote
So, given the criteria below, and the associated website with a check calculator, can you assist with the calculation? Your help is greatly appreciated!


Priority Code Scheme
Six characters
Five characters represent the customer ID. The Sixth digit will be a numeric checksum using a modification of the Luhn Algorithm.
First five digits will be Alpha-Numeric excluding the following characters. 0 (zero), O (letter OH) (to prevent print recognition confusion) B, and V (because these two letters are easily confused over the phone ) for a total of 32 possible digits.

Example:
Code R X 5 6 Z
Lookup 34 40 5 6 42
SumofDigits 73 14 40 1 6 12
Checksum 7
33554432
1048576

Here is the site: https://wiki.openmrs.org/display/docs/Check+Digit+Algorithm

Here is the code that needs tweaking:

ALTER FUNCTION [dbo].[fnGetLuhn2]
(
@Luhn VARCHAR(7999)
)
RETURNS VARCHAR(8000)
AS

BEGIN
IF @Luhn LIKE '%[^1-9AC-NP-UW-Z]%'--exclude 0,B,O,V
RETURN @Luhn


DECLARE @Index SMALLINT,
@Multiplier TINYINT,
@Sum INT,
@Plus TINYINT

SELECT @Index = LEN(@Luhn),
@Multiplier = 2,
@Sum = 0

WHILE @Index >= 1
SELECT @Plus = @Multiplier * CAST(CASE
WHEN SUBSTRING(@Luhn, @Index, 1) LIKE '[1-9]' THEN SUBSTRING(@Luhn, @Index, 1)
WHEN SUBSTRING(@Luhn, @Index, 1) LIKE '[A,C-N,P-U,W-Z]' THEN ASCII(SUBSTRING(@Luhn, @Index, 1))-48
ELSE 0 END AS TINYINT),
@Multiplier = 3 - @Multiplier,
@Sum = @Sum + @Plus / 10 + @Plus % 10,
@Index = @Index - 1

RETURN @Luhn + CASE WHEN @Sum % 10 = 0 THEN '0' ELSE CAST(10 - @Sum % 10 AS CHAR) END
END

GO


Mike Marshall
Go to Top of Page

Mike27
Starting Member

USA
6 Posts

Posted - 06/06/2012 :  16:55:32  Show Profile  Reply with Quote
Here is the working function:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create function [dbo].[udf_chkdgt](@alpha_number varchar(10))
/*****************************************************************************
Alpha-numeric check digit function.
The reference for this function is https://wiki.openmrs.org/display/docs/Check+Digit+Algorithm
*****************************************************************************/
returns varchar(10)
as
begin
declare
@length int
,@counter int
,@total_sum int
,@sum int
,@letter char(1)

set @counter=1
set @total_sum=0
set @sum=0
set @length=LEN(@alpha_number)
set @alpha_number=UPPER(@alpha_number)
while @counter<=@length
begin
set @sum=0
set @letter=substring(reverse(@alpha_number),@counter,1)
if @counter%2=0
begin
set @sum=case
when ISNUMERIC(@letter)=0 then ASCII(@letter)-48
else convert(int,@letter)
end
end
else
begin
set @sum=case
when ISNUMERIC(@letter)=0 then (2*(ASCII(@letter)-48))-9*((ASCII(@letter)-48)/5)
else 2*convert(int,@letter)-9*(convert(int,@letter)/5)
end
end
set @total_sum=@total_sum+@sum
set @counter=@counter+1
end

return @alpha_number+'-'+convert(varchar,(case
when @total_sum%10=0 then 0
else (@total_sum/10+1)*10-@total_sum
end ))
end

GO





Mike Marshall
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000