The Luhn algorithm using a recursive CTE

The other day I needed to implement the modulus 10 function in SQL.
From Wikipedia

The Luhn algorithm or Luhn 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