CREATE FUNCTION dbo.fn_ValidateCreditCard
(
@CardNumber VARCHAR(25)
)
RETURNS TABLE
AS
RETURN
WITH Cleaned AS (
SELECT
REPLACE(REPLACE(@CardNumber, ' ', ''), '-', '') AS CleanCard
),
LuhnCheck AS (
SELECT
CleanCard,
LEN(CleanCard) AS CardLength,
REVERSE(CleanCard) AS ReversedCard
FROM Cleaned
),
Calculated AS (
SELECT
CleanCard,
CardLength,
SUM(
CASE
WHEN Number % 2 = 0 THEN Digit
ELSE
CASE
WHEN Digit * 2 > 9 THEN Digit * 2 - 9
ELSE Digit * 2
END
END
) AS LuhnSum
FROM (
SELECT
CleanCard,
CardLength,
Digit = CAST(SUBSTRING(ReversedCard, Number + 1, 1) AS INT),
Number
FROM LuhnCheck
JOIN master.dbo.spt_values ON type = 'P' AND Number BETWEEN 0 AND LEN(ReversedCard) - 1
) AS Digits
GROUP BY CleanCard, CardLength
),
IssuerCheck AS (
SELECT
CleanCard,
CardLength,
CASE
WHEN CleanCard LIKE '4%' AND (CardLength = 13 OR CardLength = 16 OR CardLength = 19) THEN 'Visa'
WHEN CleanCard LIKE '5[1-5]%' AND CardLength = 16 THEN 'MasterCard'
WHEN CleanCard LIKE '222[1-9]%' OR CleanCard LIKE '22[3-9][0-9]%'
OR CleanCard LIKE '2[3-6][0-9][0-9]%' OR CleanCard LIKE '27[01][0-9]%'
OR CleanCard LIKE '2720%' AND CardLength = 16 THEN 'MasterCard'
WHEN CleanCard LIKE '3[47]%' AND CardLength = 15 THEN 'American Express'
WHEN CleanCard LIKE '6011%' OR CleanCard LIKE '65%' OR CleanCard LIKE '64[4-9]%'
AND CardLength = 16 THEN 'Discover'
ELSE 'Unknown'
END AS Issuer
FROM Calculated
),
Final AS (
SELECT
c.CleanCard AS CardNumber,
i.Issuer,
CASE WHEN c.LuhnSum % 10 = 0 THEN 1 ELSE 0 END AS IsValid
FROM Calculated c
JOIN IssuerCheck i ON c.CleanCard = i.CleanCard
)
SELECT * FROM Final;
Example Usage:
SELECT dbo.fn_IsValidCreditCardNumber('4539 1488 0343 6467') AS IsValid; -- Returns 1
SELECT dbo.fn_IsValidCreditCardNumber('1234 5678 9012 3456') AS IsValid; -- Returns 0