Credit card numbers are among the most common numbers in commercial use today. Most credit card numbers and many other numbers used in financial services use the Luhn (a.k.a Mod 10) formula for check digits. It’s been formalized as part of the ANSI X4.13 specification.
This article’s UDF is udf_Bank_IsLuhn, which validates a Luhn number such as a credit card: Here’s the CREATE FUNCTION script:
GO
SET ANSI_NULLS ON
GOCREATE FUNCTION dbo.udf_Bank_IsLuhn (
@Target varchar(20) — Number string to validate
/* Validates that a sequence of digits satisfies the Luhn
* validation formula. It’s also know as MOD 10. The full
* description is in the ANSI X4.13 specification. The Luhn
* formula is used to validate credit card numbers, Canadian
* social security numbers and many other financial services
* numbers. Another common formula is Mod 11.
*
* Dashes are removed before the number is checked. They’re
* removed from anywhere in the number so if they have to be
* in certain positions, the number should be pre-validated
* for the correct positions. False is returned for non-numerics,
* null arguments or zero length arguments.
*
* Here’s a definition of the algorithm from webopedia:
* 1) Starting with the second to last digit and moving left,
* double the value of all the alternating digits.
* 2) Starting from the left, take all the unaffected digits
* and add them to the results of all the individual digits
* from step 1. If the results from any of the numbers from
* step 1 are double digits, make sure to add the two numbers
* first (i.e. 18 would yield 1+8).
* 3) The total from step 2 must end in zero for the credit-card
* number to be valid.
*
* Example:
select CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘2323-2005-7766-3554’)
then ‘Valid’ ELSE ‘Invalid’ END
*
* Test:
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘2323-2005-7766-3554’)
then ‘Worked’ ELSE ‘ERROR’ END
SELECT CASE WHEN 0=dbo.udf_Bank_IsLuhn (‘3323-2005-7766-3554’)
then ‘Worked’ ELSE ‘ERROR’ END
SELECT CASE WHEN 0=dbo.udf_Bank_IsLuhn (‘2323-2D05-7766-3554’)
then ‘Worked’ ELSE ‘ERROR’ END
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘4111-1111-1111-1111’)
then ‘Worked’ ELSE ‘ERROR’ END — Visa
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘3400-0000-0000-009’)
then ‘Worked’ ELSE ‘ERROR’ END — Amex
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘3400-0000-0000-009’)
then ‘Worked’ ELSE ‘ERROR’ END — Amex
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘6011-0000-0000-0004’)
then ‘Worked’ ELSE ‘ERROR’ END — Discover
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘5500-0000-0000-0004’)
then ‘Worked’ ELSE ‘ERROR’ END — Master card
*
* © Copyright 2004 Andrew Novick https://novicksoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as T-SQL UDF of the Week Vol 2 #47 11/30/04
https://novicksoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
) RETURNS BIT
AS BEGIN
DECLARE @pos int
, @a int
, @b int
, @chrVal int
— Handle Null, zero length, or non-numeric input as false
IF @Target IS NULL OR LEN(@Target)=0 RETURN 0
— remove any dashes from the number.
SET @Target = REPLACE(@Target, ‘-‘, ”)
IF 0=ISNUMERIC(@Target) RETURN 0 — Must be numeric
SELECT @a = 0, @b = 0, @pos=len(@Target) — Start from end
WHILE @pos>0 BEGIN — Until the beginning
IF @pos>1 BEGIN — Not at the 1st character
SET @ChrVal= (ASCII(SUBSTRING(@Target,@pos-1,1))-48)*2
SET @a = @A + @chrVal
+ CASE WHEN @ChrVal>9 THEN -9 ELSE 0 END
END
SET @b= @b + (ASCII(SUBSTRING(@Target,@pos,1))-48)
SET @pos = @pos – 2
END — WHILE
— True if @A + @B mod 10 is zero
RETURN CASE WHEN 0 = (@a + @b) % 10 THEN 1 ELSE 0 END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The tests from the function header illustrate how the function works so lets use them as the demonstration:
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘2323-2005-7766-3554’)
then ‘Worked’ ELSE ‘ERROR’ END
SELECT CASE WHEN 0=dbo.udf_Bank_IsLuhn (‘3323-2005-7766-3554’)
then ‘Worked’ ELSE ‘ERROR’ END
SELECT CASE WHEN 0=dbo.udf_Bank_IsLuhn (‘2323-2D05-7766-3554’)
then ‘Worked’ ELSE ‘ERROR’ END
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘4111-1111-1111-1111’)
then ‘Worked’ ELSE ‘ERROR’ END — Visa
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘3400-0000-0000-009’)
then ‘Worked’ ELSE ‘ERROR’ END — Amex
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘3400-0000-0000-009’)
then ‘Worked’ ELSE ‘ERROR’ END — Amex
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘6011-0000-0000-0004’)
then ‘Worked’ ELSE ‘ERROR’ END — Discover
SELECT CASE WHEN 1=dbo.udf_Bank_IsLuhn (‘5500-0000-0000-0004’)
then ‘Worked’ ELSE ‘ERROR’ END — Master card
GO
Results:
Worked
Worked
Worked
Worked
Worked
Worked
Luhn is one of the more common validations that an e-commerce application must implement. Putting the validation in the database may or may not be the best place but there are times when you need validation at multiple levels of the application including the database.
Update (05/03/07)
A reader sent this UDF, that calculates the check digit, instead of checking that it’s correct.
returns int
as
begin
–Declare internal variables
declare @Target varchar(20), @Digit int, @Pos int, @Total int, @Multiplier int–Initialize variables
set @Target=convert(varchar(15), @SourceID)
select @Pos=len(@Target), @Multiplier = 2, @Total = 0
–Loop from right-to-left through the SourceID
while @Pos > 0 begin
— Retrieve digits right-to-left and multiply by the multiplier
set @Digit = convert(int, substring(@Target, @Pos, 1)) * @Multiplier
— Add to Total summing 2 digit result together
set @Total = @Total + case when @Digit > 9 then @Digit – 9 else @Digit end
— Alternate multiplier for odd and even digits
set @Multiplier = case when @Multiplier = 2 then 1 else 2 end
— Continue to the next left-most digit
set @Pos = @Pos – 1
end — WHILE
–Return the check digit (10 – Total MOD 10)
return case when 10 – @Total % 10 = 10 then 0 else 10 – @Total % 10 end
end
go