IS_EMAIL

Return true if the input string is a syntactically valid email address.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

IS_EMAIL(str)

Description

## Overview Returns TRUE if the input string looks like a valid email address, FALSE if it does not, and NULL if the input is NULL. Use this function at ingest to reject clearly malformed addresses, to partition rows into clean and quarantine lanes, or to guard a downstream normalization step. This is a pragmatic structural check, not a full RFC 5322 grammar parser. It catches the common failure modes you see in user input (missing `@`, missing domain, trailing whitespace, stray spaces) while staying fast enough to apply to large ingest streams. ## Validation rules - The string must contain exactly one `@` separator with a non-empty local part and a non-empty domain part. - The local part may contain letters, digits, dot, underscore, hyphen, and plus sign. - The domain part must contain at least one dot and end with a letter-only top-level label of length 2 or more. - Leading and trailing whitespace causes the check to return FALSE; trim the input first if you want to accept it. - Internationalized (IDN) domains and quoted local parts are accepted in their ASCII or punycode form only. ## Behavior - Returns NULL for a NULL input; otherwise always returns TRUE or FALSE. - Case insensitive for the domain part; the local part is compared verbatim. - Deterministic and side effect free; no DNS lookup is performed. - Empty strings return FALSE. - Does not verify deliverability, mailbox existence, or whether the domain has MX records. ## Compatibility - DeltaForge uses a pragmatic subset of RFC 5322. Exotic but technically legal addresses (for example, quoted local parts with embedded spaces) are rejected. If you need full RFC 5322 compatibility, apply a custom UDF downstream.

Parameters

Name	Type	Description
`str`		Specifies the string to validate. Validation is purely structural and does not contact any mail server.

Examples

-- Simple valid address
SELECT IS_EMAIL('user@example.com');  -- true

-- Missing @ symbol
SELECT IS_EMAIL('userexample.com');  -- false

-- Missing domain after @
SELECT IS_EMAIL('user@');  -- false

-- NULL propagation
SELECT IS_EMAIL(NULL);  -- NULL

-- Filter unparseable email values during ingest
SELECT contact_id, email
FROM crm.stage.raw_contacts
WHERE NOT IS_EMAIL(email);

-- Guard a downstream cast
SELECT CASE WHEN IS_EMAIL(raw_email) THEN LOWER(raw_email) END AS normalized_email
FROM crm.stage.raw_contacts;

Pitfalls

IS_EMAIL validates structure only. It does not verify that the address exists, that the domain has MX records, or that the mailbox accepts mail. Pair it with a delivery-time check if you need real deliverability.
Leading or trailing whitespace makes a valid address look invalid. Always TRIM or CLEAN_SPACES before calling IS_EMAIL if the source data might contain stray spaces.
The grammar accepted is a pragmatic subset, not the full RFC 5322 production. Quoted local parts, comments, and UTF-8 local parts are rejected. Document the expected input shape upstream if you rely on this behavior.
A NULL input produces a NULL result, not FALSE. Predicates like `WHERE NOT IS_EMAIL(email)` exclude NULL rows; use `WHERE email IS NULL OR NOT IS_EMAIL(email)` to also catch missing values.