surface↓cleaning↓valid
Cleaningscalar · returns boolean

VALID

Check whether a value matches the expected format for a type

Per-row — runs once for each row.

cleaningllmtext

Syntax

VALID({{ value }}, '{{ type }}')
{{ value }} IS VALID '{{ type }}'
{{ value }} VALID AS '{{ type }}'

Arguments

nametypedescription
valueVARCHAR—
typeVARCHARFormat type: email, phone, url, date, ssn, zip, uuid, ip, credit_card, etc.

About

Check whether a value matches the expected format for a given type — email, phone, date, URL, SSN, credit card, and so on. Returns TRUE/FALSE, handy as a WHERE filter for data-quality sweeps. Unlike a hand-rolled regex or CHECK constraint, VALID applies the operator-backed semantic rules for each type, so it catches edge cases (Unicode in email local-parts, valid-but-rare phone formats) that regexes typically miss. For a free-form rule beyond the built-in type list, use VALIDATE — it takes a plain-English rule instead of a type name.

Examples

Function: Valid email returns true

SELECT
  valid ('john@gmail.com', 'email')

Function: Invalid email returns false

SELECT
  valid ('not-an-email', 'email')

Function: Valid US phone returns true

SELECT
  valid ('(555) 123-4567', 'phone')

Function: Valid US zip returns true

SELECT
  valid ('02101', 'zip')

Infix IS VALID: filters valid emails

WITH
  t AS (
    SELECT
      'user@example.org' AS email
  )
SELECT
  email
FROM
  t
WHERE
  email IS VALID 'email'

Infix IS VALID: returns false for invalid

WITH
  t AS (
    SELECT
      'invalid' AS email
  )
SELECT
  CASE
    WHEN email IS VALID 'email' THEN 'yes'
    ELSE 'no'
  END
FROM
  t

Infix VALID AS: validates IP address

WITH
  t AS (
    SELECT
      '10.0.0.1' AS ip
  )
SELECT
  ip
FROM
  t
WHERE
  ip VALID AS 'ip'

Nearby rabbit holes

same domain
scalar

ANONYMIZE

Remove or mask personally identifiable information from text

cleaningllmtext
scalar

AS_SMART

Type-cast messy real-world values that trip up standard CAST

cleaningllmtext
scalar

CANONICAL

Return the canonical/official form of a value (auto-detects entity type)

cleaningllmtext
scalar

CLEAN_YEAR

Extracts 4-digit year from messy text, returns -1 if undetermined

cleaningllmdate
scalar

CLEAN_YEAR_LLM

LLM-backed year extraction (escape hatch for CLEAN_YEAR)

cleaningllmllm-escape-hatchdate
aggregate

COALESCE_SMART

Pick the best non-null value from a group (quality-aware COALESCE)

cleaningllmtext
Climb back to The Looking Glass