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

SPLIT_PART: extract part of delimited string

The query

SQL
SELECT
  email,
  SPLIT_PART(email, '@', 1) AS username,
  SPLIT_PART(email, '@', 2) AS domain,
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', 1) AS domain_name
FROM customers
WHERE email IS NOT NULL;
Tested against PostgreSQL 16

Note

“SPLIT_PART(string, delimiter, field) — field is 1-based. Non-existent field returns empty string.

Tables referenced