TO_SNAKE_CASE

Convert the input string to snake_case.

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

TO_SNAKE_CASE(str)

Description

## Overview Converts the input string to snake_case by lowercasing the entire string and inserting underscores at word boundaries. Word boundaries include the transition from lowercase to uppercase, as well as spaces and hyphens. Use this function to turn JSON or display names into database-friendly column names. ## Behavior - Returns NULL if the input is NULL. - Empty input returns an empty string. - Uppercase transitions insert an underscore before the uppercase letter. - Spaces and hyphens are replaced with a single underscore. - Consecutive separators collapse into a single underscore. - Leading and trailing underscores are trimmed. - Deterministic and side effect free. ## Compatibility - Matches the PG-compat TO_SNAKE_CASE semantics.

Parameters

Name	Type	Description
`str`		Specifies the input string. Uppercase boundaries, spaces, and hyphens are converted into underscores.

Examples

-- From camelCase
SELECT TO_SNAKE_CASE('firstName');  -- 'first_name'

-- From spaces
SELECT TO_SNAKE_CASE('Hello World');  -- 'hello_world'

-- From PascalCase
SELECT TO_SNAKE_CASE('BackgroundColor');  -- 'background_color'

-- Normalize JSON keys into SQL column names
SELECT TO_SNAKE_CASE(json_key) AS db_column
FROM stage.raw.schema_probe;

-- NULL propagation
SELECT TO_SNAKE_CASE(NULL);  -- NULL

Pitfalls

Acronyms are not special-cased. `TO_SNAKE_CASE('XMLParser')` returns `x_m_l_parser`, which is usually not what you want. Preprocess acronyms to lowercase first if you need `xml_parser`.
The function recognizes only casing transitions, spaces, and hyphens as boundaries. Other punctuation is preserved in place, which may produce unexpected column names.
Unicode case folding follows the engine default. In locales where casing is ambiguous (for example, Turkish `I`/`i`), results may differ from expectations; apply LOWER in a specific locale upstream if needed.
Double-applying the function is idempotent on pure snake_case input but may trim separators differently when fed a string that already contains underscores in unusual positions.