Technical Article

Credit Card Number Validation Escalar Function

,

Usage:

select dbo.validateCreditCard (@ccNumber). Where @ccNumber is a 16 digit lenght number in char format.

The function returns a bit datatype (1: valid number, or 0: invalid number)

 

CREATE FUNCTION dbo.validateCreditCard (
@CCNumbervarchar(20)
)
RETURNS bit AS
BEGIN 
declare@lenCCNumberint,
@iint,
@TotalSumint,
@tempint,
@isValidCCbit

SET@isValidCC = 0
SET @CCNumber = LTRIM(RTRIM(@CCNumber))

IF (@CCNumber IS NULL) OR (LEN(@CCNumber) = 0) OR (ISNUMERIC(@CCNumber) <> 1)
GOTO Rtrn

SET @lenCCNumber=LEN(@CCNumber)
SET @i = 1
SET @temp = 0
SET @TotalSum = 0

WHILE @i <= @lenCCNumber
BEGIN
IF SUBSTRING(@CCNumber,@i,1) NOT LIKE '[0-9]'
GOTO Rtrn

SET @temp = CAST(SUBSTRING(@CCNumber,@i,1) AS int)
IF (@i % 2) = 1
BEGIN
SET @temp = @temp * 2
IF @temp > 9
SET @temp = @temp - 9
END

SET @TotalSum = @TotalSum + @temp
SET @i = @i + 1
END

IF (@TotalSum % 10 = 0) AND (@TotalSum <= 150)
SET @isValidCC = 1
Rtrn:
RETURN (@isValidCC)
END

Rate

3.33 (6)

Share

Share

Rate

3.33 (6)