LUHN_CHECK

Return true if the input digit string passes the Luhn (mod-10) checksum.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

LUHN_CHECK(str)

Description

## Overview Returns TRUE if the input numeric string passes the Luhn (mod-10) checksum, FALSE otherwise, and NULL for a NULL input. The Luhn algorithm is used to detect common transcription errors in identification numbers such as credit cards, IMEI numbers, and some national identifiers. Use this function to weed out obvious typos at ingest. LUHN_CHECK validates checksum correctness only. It does not confirm that the card or IMEI is real, issued, or active. ## Validation rules - Input must be a string of decimal digits with no separators. - Starting from the rightmost digit, every second digit is doubled; if the doubled value exceeds 9, its digits are summed. - The sum of all digits must be divisible by 10 for the check to pass. - Strings shorter than two digits are rejected. - Leading and trailing whitespace is rejected; strip first with TRIM or REGEXP_REPLACE. ## Behavior - Returns NULL for NULL input; otherwise always TRUE or FALSE. - Deterministic and side effect free. - Does not validate card issuer, expiration, or brand. - Does not detect all swapped-digit errors; Luhn catches single-digit errors and most adjacent transpositions but not all. ## Compatibility - Implements the standard mod-10 algorithm used by ISO/IEC 7812 for payment cards and by 3GPP for IMEI numbers.

Parameters

Name	Type	Description
`str`		Specifies the numeric string to validate. Must consist only of digit characters; spaces and hyphens should be stripped upstream.

Examples

-- Valid credit card number (test vector)
SELECT LUHN_CHECK('4539578763621486');  -- true

-- Same number with one digit changed fails
SELECT LUHN_CHECK('4539578763621487');  -- false

-- Validate an IMEI
SELECT LUHN_CHECK('490154203237518');  -- true

-- Validate after stripping separators
SELECT LUHN_CHECK(REGEXP_REPLACE(card_number, '[^0-9]', '')) AS ok
FROM payments.stage.cards;

-- Reject suspicious rows during ingest
SELECT card_id FROM payments.stage.cards WHERE NOT LUHN_CHECK(card_number);

-- NULL propagation
SELECT LUHN_CHECK(NULL);  -- NULL

Pitfalls

LUHN_CHECK validates the checksum only. A passing result does not prove the card is real, active, or issued by a legitimate network. Never use Luhn as your sole fraud signal.
Separators (spaces, hyphens) cause LUHN_CHECK to return FALSE. Normalize the input with `REGEXP_REPLACE(col, '[^0-9]', '')` before calling the function.
The algorithm does not catch every possible swap. The transposition `09 -> 90` is detected, but `22 -> 22` (no-change) obviously is not. Combine Luhn with length and issuer-prefix checks for stronger validation.
A NULL input returns NULL, not FALSE. Handle the NULL case explicitly when using LUHN_CHECK in a WHERE clause.