Technical Article

Add Check Digit (LUHN)

,

If you ever need to create a credit card like number this stored procedure is for you.

Most credit cards contain a check digit, which is the digit at the end of the credit card number.

To generate the check digit, the LUHN formula is applied to the number. To validate the credit-card number, the check digit is figured into the formula.

Here's how the algorithm works for verifying credit cards; the math is quite simple:

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). Basically, your equation will look like a regular addition problem that adds every single digit.

3) The total from step 2 must end in zero for the credit-card number to be valid.

Additional resources:
http://www.webopedia.com/TERM/L/Luhn_formula.html
http://www.beachnet.com/~hstiles/cardtype.html
http://euro.ecom.cmu.edu/resources/elibrary/everycc.htm

create procedure sp_AddCheckDigid
@num varchar(16) output
as

if Len(Ltrim(Rtrim(@num))) <> 15 RETURN -1
 
declare 
@num_temp_char varchar(2),
@num_temp_int int,
@num_total int,
@index int
select @index = 1, @num_total = 0
 
While @index <= Len(@num)
Begin
 Set @num_temp_char = SUBSTRING ( @num , @index , 1 ) 
 If (@index % 2) <> 0
 Begin
set @num_temp_int = Cast(@num_temp_char as int)
set @num_temp_int = @num_temp_int * 2
set @num_temp_char = Cast(@num_temp_int as varchar(2))
if Len(@num_temp_char) = 2
set @num_total = @num_total + (Cast(Left(@num_temp_char,1) as int) + Cast(Right(@num_temp_char,1) as int))
else
set @num_total = @num_total + Cast(@num_temp_char as int)
 End
 Else
 Begin
  set @num_total = @num_total + Cast(@num_temp_char as int)
 End
 set @index = @index + 1
End
 
Set @num_temp_char = Cast(10 - Cast(Right(Cast(@num_total as varchar(2)),1) as int) as varchar(2))
Set @num = @num + @num_temp_char 




GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

What is the Maximum Page Size in SQL Server 2000?

I have always read that 8060 bytes is the maximum size. This is stated over and over again in Books Online, the MS site and numerous other sites, including this one. However a post in our forum recently questioned this. I decided to verify the problem and do a little research.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.55 (11)

You rated this post out of 5. Change rating

2007-10-02 (first published: )

37,303 reads

External Article

SQL Server 2000 User-Defined Functions White Paper

The User_Defined_Functions.exe file contains the User-Defined Functions white paper. The User-Defined functions white paper outlines the characteristics of the new user-defined function (UDF) feature that is introduced in Microsoft SQL Server 2000. The white paper also summarizes how you can create your own Transact-SQL functions to extend the programmability of Transact-SQL.

2001-08-31

2,233 reads

External Article

Work Around Errors in Database Web Apps

ne of the issues you face when building Web applications is handling the errors you encounter when interacting with a back-end database. I was recently working with someone to create a new Web site with SQL Server™, ActiveX® Data Objects (ADO), and ASP. Lots of little things came up that I thought were worth sharing with MIND readers, so I'll focus this column on what I learned from this experience and the solutions to many of the problems I faced.

2001-07-13

1,579 reads