How to Validate Credit Card Numbers Using SQL and the Luhn Algorithm?

 Ever wondered how online systems instantly verify your credit card number? They use an algorithm called the Luhn Algorithm (often referred to as the Mod 10 Algorithm). It’s a clever way to validate credit card numbers to ensure they're legitimate. In this guide, we’ll break down the logic behind the algorithm and show how to write SQL queries to implement it.

What Is the Luhn Algorithm?

The Luhn Algorithm is a basic checksum formula used to validate identification numbers, including credit card numbers. It works by checking if the number meets certain mathematical conditions. Credit card numbers are typically 14 to 16 digits long, but this can vary.

Here’s how the digits are structured:

1. First six digits: The Issuer Identification Number (IIN), which identifies the card provider (e.g., Visa, Mastercard).
2. Digits 7 to second-to-last: The customer’s account number.
3. Final digit: The check digit, calculated using the Luhn Algorithm.

The check digit helps determine if the sequence of numbers is valid.

Steps in the Luhn Algorithm

To validate a credit card number with the Luhn Algorithm, follow these steps:

1. Start from the rightmost digit (excluding the check digit). Take every second digit (even positions from the right).
2. Multiply these digits by 2.
3. If the result of multiplying is a double-digit number, sum up the digits of that result. For example, 18 becomes 1 + 8 = 9.
4. Add the modified digits to the remaining digits from odd positions (from the right).
5. Calculate the total sum.
6. Check if the total sum is divisible by 10. If it is, the number is valid. If not, it’s invalid.

This logic ensures the number follows a valid pattern defined by the issuers.

Writing SQL to Implement the Luhn Algorithm

Now, let's translate these steps into SQL. We'll use a credit card number as input and validate it using queries.

Extracting Digits

We need to extract digits at odd and even positions from the right side of the number. For this, recursive Common Table Expressions (CTEs) in SQL come in handy.

• Step 1: Define a starting position. For even digits, start at 2 (second from the right). For odd digits, start at 1 (first from the right).
• Step 2: Use the SUBSTRING function to extract the digit at each position. Increment the position by 2 to get each subsequent even or odd digit.
• Step 3: Reverse the string to simplify this process since SQL defaults to extracting from the left.

Here’s how it looks in SQL:

WITH RecursiveCTEEven AS (
    SELECT 
        2 AS Position, 
        CAST(SUBSTRING(REVERSE(CreditCardNumber), 2, 1) AS INT) AS Digit
    UNION ALL
    SELECT 
        Position + 2, 
        CAST(SUBSTRING(REVERSE(CreditCardNumber), Position + 2, 1) AS INT) 
    FROM RecursiveCTEEven
    WHERE Position + 2 <= LEN(CreditCardNumber)
),
RecursiveCTEOdd AS (
    SELECT 
        1 AS Position, 
        CAST(SUBSTRING(REVERSE(CreditCardNumber), 1, 1) AS INT) AS Digit
    UNION ALL
    SELECT 
        Position + 2, 
        CAST(SUBSTRING(REVERSE(CreditCardNumber), Position + 2, 1) AS INT)
    FROM RecursiveCTEOdd
    WHERE Position + 2 <= LEN(CreditCardNumber)
)

Applying Multiplication

After extracting even digits, multiply them by 2:

SELECT 
    Digit * 2 AS MultipliedDigit
FROM RecursiveCTEEven

When the result is a double-digit, we need to sum the digits of that number:

SELECT 
    CASE 
        WHEN (Digit * 2) > 9 THEN (Digit * 2) / 10 + (Digit * 2) % 10
        ELSE Digit * 2
    END AS AdjustedDigit
FROM RecursiveCTEEven

Summing Up All Digits

Combine the results from even and odd digit extractions into a final sum. This includes both adjusted even digits and unmodified odd digits:

WITH AllDigits AS (
    SELECT Digit FROM RecursiveCTEOdd
    UNION ALL
    SELECT 
        CASE 
            WHEN (Digit * 2) > 9 THEN (Digit * 2) / 10 + (Digit * 2) % 10
            ELSE Digit * 2
        END 
    FROM RecursiveCTEEven
)
SELECT 
    SUM(Digit) AS TotalSum
FROM AllDigits

Checking Validity

Finally, check if the total sum is divisible by 10 using the modulo operator (%):

SELECT 
    CASE 
        WHEN (SUM(Digit) % 10) = 0 THEN 'Valid'
        ELSE 'Invalid'
    END AS Validity
FROM AllDigits

Putting It All Together

Combining everything, you’ve shown the logic to extract digits, modify them as per the algorithm, and validate the credit card number. The output will confirm if the credit card number is valid or invalid.

Enhancements

Want to take this further? You can identify the card provider (like Visa or Mastercard). Look at the Issuer Identification Number (first six digits) and apply custom logic to label the card type.

For instance:

SELECT 
    CASE 
        WHEN LEFT(CreditCardNumber, 1) = '4' THEN 'Visa'
        WHEN LEFT(CreditCardNumber, 1) = '5' THEN 'Mastercard'
        ELSE 'Unknown'
    END AS CardType
FROM YourTable

Wrapping Up

The Luhn Algorithm ensures credit card numbers follow a valid pattern. Using SQL, you can implement this step-by-step process to validate these numbers programmatically. Whether it's for data validation, portfolio projects, or improving SQL skills, this is a practical application.

Try it out! Test different numbers, validate them, and even customize the query to identify card providers. It’s a great way to level up your SQL expertise while solving real-world problems.

Let us know if this guide was helpful, and don’t forget to share it with others who love exploring SQL!