IBAN_CHECK

Return true if the input string is a valid International Bank Account Number.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

IBAN_CHECK(str)

Description

## Overview Returns TRUE if the input string is a valid International Bank Account Number as defined by ISO 13616, FALSE otherwise, and NULL for a NULL input. Use this function at ingest to reject obviously malformed bank account numbers, before attempting a wire transfer integration, or as a cheap guard before persisting payment details. ## Validation rules - The input is normalized by removing internal spaces and uppercasing letters. - The first two characters must be a letter-only country code. - The next two characters must be decimal check digits. - The total length must match the country-specific length defined by the IBAN registry. - The alphanumeric body is converted to a numeric value using the standard letter-to-digit mapping (A = 10, B = 11, ..., Z = 35). - The converted number modulo 97 must equal 1 for the IBAN to pass. ## Behavior - Returns NULL for NULL input; otherwise always TRUE or FALSE. - Deterministic and side effect free. - Does not verify that the account exists, that the bank identifier is real, or that funds are available. ## Compatibility - Implements the full ISO 13616 check including country-length validation. Unknown country codes are rejected even if the mod-97 check would pass.

Parameters

Name	Type	Description
`str`		Specifies the IBAN string to validate. Spaces are ignored and case is normalized to uppercase before validation.

Examples

-- Valid German IBAN
SELECT IBAN_CHECK('DE89370400440532013000');  -- true

-- Valid UK IBAN with space separators
SELECT IBAN_CHECK('GB29 NWBK 6016 1331 9268 19');  -- true

-- Invalid check digits
SELECT IBAN_CHECK('DE00370400440532013000');  -- false

-- Wrong country-length combination
SELECT IBAN_CHECK('DE893704004405320130');  -- false

-- Filter malformed IBANs at ingest
SELECT account_id FROM payments.stage.bank_accounts WHERE NOT IBAN_CHECK(iban);

-- NULL propagation
SELECT IBAN_CHECK(NULL);  -- NULL

Pitfalls

IBAN_CHECK validates structure and check digits only. A passing IBAN may still point to a closed or non-existent account. Use a separate account-lookup service for deliverability.
Country-length validation is strict. An IBAN with a valid mod-97 value but an incorrect length for its country code is rejected.
The function normalizes whitespace and case, but does not strip other punctuation. Characters such as periods or non-breaking spaces cause validation to fail; normalize upstream.
Check digits are the only internal validator. A digit transposition in the body of the account number is often detected, but not all such errors are caught.