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.

09

Chapter 9

String Functions

Text in Postgres. Concatenation, casing, trimming, regex, and the functions that quietly do most of the work.

In this chapter

19

queries, easy → advanced

  1. 001UPPER, LOWER, INITCAP: case functionsRead
  2. 002LENGTH and CHAR_LENGTHRead
  3. 003TRIM, LTRIM, RTRIM: remove whitespaceRead
  4. 004CONCAT and || operator for string buildingRead
  5. 005SPLIT_PART: extract part of delimited stringRead
  6. 006REPLACE and REGEXP_REPLACERead
  7. 007FORMAT: printf-style string formattingRead
  8. 008REGEXP_MATCHES: extract all regex matchesRead
  9. 009LEVENSHTEIN: fuzzy matching distanceRead
  10. 010TO_TSVECTOR + TO_TSQUERY: full-text searchRead
  11. 011Array aggregation and unnesting patternsRead
  12. 012Concatenate first and last name with CONCAT_WSRead
  13. 013Extract domain from email addressesRead
  14. 014Pad strings to fixed width for reportsRead
  15. 015Extract numbers from alphanumeric stringsRead
  16. 016Generate slugs from product namesRead
  17. 017Parse structured strings into columnsRead
  18. 018JSON-like string parsing with SUBSTRING and POSITIONRead
  19. 019Soundex and metaphone for phonetic matchingRead