Click here to Skip to main content
15,125,273 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 23 Oct 2010

Tagged as

Stats

23.4K views
12 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
4.33/5 (4 votes)
23 Oct 2010CPOL
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).
SQL
-- 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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rajesh Anuhya
Architect
India India
No Biography provided

Comments and Discussions

 
GeneralReason for my vote of 3 Interesting exercise in T-SQL progra... Pin
Rod Kemp23-Oct-10 4:49
MemberRod Kemp23-Oct-10 4:49 
GeneralThanks Sandeep.., i want some more help from u, to edit my a... Pin
Rajesh Anuhya23-Oct-10 2:17
professionalRajesh Anuhya23-Oct-10 2:17 
GeneralReason for my vote of 5 Ok..i Have to admit..This is great. ... Pin
GPUToaster™23-Oct-10 2:16
MemberGPUToaster™23-Oct-10 2:16 
Reason for my vote of 5
Ok..i Have to admit..This is great. Well done.
GeneralEdited, Formatted and published! Pin
Sandeep Mewara23-Oct-10 1:55
mvaSandeep Mewara23-Oct-10 1:55 
GeneralOk i will update in tip/trick Pin
Rajesh Anuhya23-Oct-10 0:01
professionalRajesh Anuhya23-Oct-10 0:01 
GeneralOk. Can you please add what is 'Check Digit(mod10)'? Can you... Pin
Sandeep Mewara22-Oct-10 23:35
mvaSandeep Mewara22-Oct-10 23:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.