15,125,273 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 23 Oct 2010

23.4K views
12 bookmarked

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

Rate me:
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".

## Share

 Architect India
No Biography provided

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