From Wikipedia

TheLuhn algorithmorLuhn formula, also known as the "modulus 10" or "mod 10" algorithm, is a simple checksum formula used to validate a variety of identification numbers, such as credit card numbers and Canadian Social Insurance Numbers. It was created by IBM scientist Hans Peter Luhn and described in U.S. Patent 2,950,048 , filed on January 6, 1954, and granted on August 23, 1960.

I found this rather nice recursive way to do it in SQL Server 2005

CREATE FUNCTION dbo.Modulus10 (@num INT)

RETURNS INT

AS

BEGIN

DECLARE @result INT;

DECLARE @snum VARCHAR(100);

SET @snum = CAST(@num AS VARCHAR (100)); -- converts num to a string value

WITH Partials(n, odd, even)

AS

(

SELECT LEN(@snum)+2 AS n, -- Ignores the first row by starting at offset + 2 to make the code more readable

0 AS odd,

0 AS even

UNION ALL

SELECT n - 2, -- "loop" counter

(CAST(COALESCE(SUBSTRING(@snum, n - 2 , 1),'0') AS INT) -- Extracting the n'th odd digit

* 2 / CAST (10 as INT))

+

(CAST(COALESCE(SUBSTRING(@snum, n - 2, 1),'0') AS INT) -- Extracting the n'th odd digit

* 2 % 10) AS odd,

CAST (COALESCE (SUBSTRING(@snum, n - 3, 1), '0') AS INT) AS even -- Extracting the n'th even digit

FROM Partials

WHERE n >= 1)

SELECT @result =(CASE (sum(odd + even) % 10)

WHEN 0 THEN (sum(odd + even) % 10) ELSE 10 -(sum(odd + even) % 10)

END)

FROM Partials;

RETURN(@result)

END

Listen to Boheme Radio!

Delphi programming RuleZ

## No comments:

Post a Comment