12,945,066 members (50,821 online)
Tip/Trick
alternative version

#### Stats

15.9K views
12 bookmarked
Posted 23 Oct 2010

# SQL function to verify 'Check Digit' of given VISA/Master Card

, 23 Oct 2010 CPOL
 Rate this:
It help's to verify if the card number provided by the user is valid or not.
Hi,

Normally, every Visa/Master card have the 16 digits number printed(embossed) on the card. Last 16th digit is called as check digit.

There is an algorithm called MOD10 to verify that card number is valid or not. Here I am providing a link for detailed description about check digits logic: Cards Check Digits[^]

I am providing a SQL Server function for 'Check Digit' for Visa/Master Cards(16 Digits).
```-- About using function: Pass the first 15 digits of the card no to the function
-- and function will retuns the last check digit.
-- That returned value should match with user provided 16th digit.

CREATE FUNCTION [DBO].[MOD10CHECKDIGIT](@STR VARCHAR(15))
RETURNS VARCHAR(1)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @RETURNSTR VARCHAR(100);
DECLARE @REVERSESTR VARCHAR(15);
DECLARE @CNT INT;
DECLARE @SUMVAL INT;
DECLARE @TEMPVAL INT;
DECLARE @TEMPVAL1 INT;
IF(LEN(@STR)<>15)
SET @RETURNSTR='NO';
ELSE
BEGIN
SET @REVERSESTR = REVERSE(@STR);
SET @SUMVAL=0;
SET @CNT=1;
WHILE @CNT < 16
BEGIN
SET @TEMPVAL =0;
SET @TEMPVAL1 =0;
IF((@CNT%2)<>0)
SET @TEMPVAL =  (CONVERT(INT,SUBSTRING(@REVERSESTR,@CNT,1) * 2));
ELSE
SET @TEMPVAL =  (CONVERT(INT,SUBSTRING(@REVERSESTR,@CNT,1)));
IF(@TEMPVAL>9)
BEGIN
SET @TEMPVAL1 = CONVERT(INT,SUBSTRING(CONVERT(VARCHAR,@TEMPVAL),1,1)) +
CONVERT(INT,SUBSTRING(CONVERT(VARCHAR,@TEMPVAL),2,1));
SET @TEMPVAL=@TEMPVAL1;
END;
SET @SUMVAL = @SUMVAL + @TEMPVAL;
SET @CNT= @CNT + 1;
END
SET @RETURNSTR=CONVERT(VARCHAR,(10 - (@SUMVAL%10)));
--SET @RETURNSTR=CONVERT(VARCHAR,@SUMVAL);
END
RETURN @RETURNSTR;
END```

Note: Every VISA card starts with digit "4" and Mastercard stats with "5".

## Share

 Software Developer (Senior) India
No Biography provided

## You may also be interested in...

 First Prev Next
 Reason for my vote of 3 Interesting exercise in T-SQL progra... Rod Kemp23-Oct-10 3:49 Rod Kemp 23-Oct-10 3:49
 Thanks Sandeep.., i want some more help from u, to edit my a... Rajesh Puli23-Oct-10 1:17 Rajesh Puli 23-Oct-10 1:17
 Reason for my vote of 5 Ok..i Have to admit..This is great. ... GPUToaster23-Oct-10 1:16 GPUToaster 23-Oct-10 1:16
 Edited, Formatted and published! Sandeep Mewara23-Oct-10 0:55 Sandeep Mewara 23-Oct-10 0:55
 Ok i will update in tip/trick Rajesh Puli22-Oct-10 23:01 Rajesh Puli 22-Oct-10 23:01
 Ok. Can you please add what is 'Check Digit(mod10)'? Can you... Sandeep Mewara22-Oct-10 22:35 Sandeep Mewara 22-Oct-10 22:35
 Last Visit: 31-Dec-99 18:00     Last Update: 23-May-17 5:15 Refresh 1