Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

IntermediateString Functions

REPLACE and REGEXP_REPLACE

The query

SQL
SELECT
  name,
  REPLACE(name, ' ', '_') AS snake_case,
  REGEXP_REPLACE(name, '[^a-zA-Z0-9]', '', 'g') AS alphanumeric_only,
  REGEXP_REPLACE(name, '(\w+)', '\1', 'g') AS words_captured
FROM products;
Tested against PostgreSQL 16

Note

“'g' flag in REGEXP_REPLACE = global (replace all matches). Default replaces only first.

Tables referenced