Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

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

0.00/5 (No votes)
23 Oct 2010 1  
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".

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here