Chapter 12 · basic · 7 min
String functions
Real-world text data is messy: stray whitespace, inconsistent casing, names glued together that should be apart. SQL's string functions let you clean and reshape text right in a query, instead of exporting to a spreadsheet.
We'll tidy up an imported product feed.
The dataset
A products table where raw_name has leading/trailing whitespace and inconsistent casing, exactly what a messy import feed looks like.
Schema
| id | INT |
| raw_name | TEXT |
Example data
TRIM: remove stray whitespace
TRIM(text) strips leading and trailing whitespace, but leaves internal spaces alone. Imported data almost always needs this before anything else.
Before you run it — does TRIM touch the space between 'Air' and 'Max' in the first row, or only the edges?
Why untrimmed text is a silent bug
Untrimmed whitespace doesn't just look wrong in a report. It breaks comparisons the same way NULL does, except worse, because it looks completely identical to the correct value on screen. WHERE raw_name = 'Nike Air Max 90' will not match ' Nike Air Max 90 ', and there's no visual way to spot the difference in a results grid. Always assume text from an import, form submission, or upload has stray whitespace until proven otherwise.
UPPER and LOWER: normalize case
UPPER(text) and LOWER(text) force consistent casing, useful both for display and for comparisons, since 'Nike' = 'nike' is false in SQL but LOWER('Nike') = LOWER('nike') is true.
Before you run it — what does 'Puma RS-X' become in shout_case and quiet_case?
CONCAT and ||: combine strings
CONCAT(a, b, ...) joins strings together; Postgres also supports the || operator for the same thing. Handy for building labels like a full display name from separate pieces.
Before you run it — what does the label look like for the row with id 2?
SPLIT_PART: pull out one piece
SPLIT_PART(string, delimiter, n) splits on a delimiter and returns the nth chunk. Here, splitting on a space and taking chunk 1 pulls out the brand.
Before you run it — splitting 'Puma RS-X' on space and taking chunk 1, what's the brand?
LENGTH, REPLACE, and POSITION
Three more that round out the toolkit:
| Function | Purpose | Example | Result |
|---|---|---|---|
LENGTH(text) | count characters | LENGTH('Puma') | 4 |
REPLACE(text, from, to) | swap every occurrence of a substring | REPLACE('Air Max','Air','A') | A Max |
POSITION(sub IN text) | 1-based index of first match, or 0 | POSITION('Max' IN 'Air Max') | 5 |
A 0 result from POSITION means "not found", not an error.
Before you run it — for the adidas and Puma rows, which have no "Max" in them, what does max_starts_at show?
If you've used Excel or Google Sheets
Every function here has a near-identical spreadsheet cousin: TRIM() is the same name and job in both; UPPER()/LOWER() are identical; CONCAT() or the & operator does what || does; SPLIT_PART is closest to Excel's Text-to-Columns or Sheets' SPLIT(); LENGTH maps to LEN(); REPLACE maps to SUBSTITUTE(); POSITION maps to FIND().
Ready to practice? 99 Speedmart's product name cleanup question below strings several of these together, exactly like the examples here.
Text cleanup is unglamorous but constant in real interviews; expect at least one question that hinges on TRIM, LOWER, or pulling a substring out of a messier field. Next: dates and timestamps.