Please start any new threads on our new
site at http://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.

Our new SQL Server Forums are live!
Come on over! We've restricted the ability to create new threads on these forums.

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

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.

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.

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

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