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.
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:
| raw_name | clean_name |
|---|---|
Nike Air Max 90 | Nike Air Max 90 |
adidas Ultraboost 22 | adidas Ultraboost 22 |
Puma RS-X | Puma RS-X |
Before and after TRIM
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:
| shout_case | quiet_case |
|---|---|
| NIKE AIR MAX 90 | nike air max 90 |
| ADIDAS ULTRABOOST 22 | adidas ultraboost 22 |
| PUMA RS-X | puma rs-x |
Same text, two cases
CONCAT and ||: combine strings
CONCAT(a, b, ...) joins strings together; Postgres also supports the || operator for the same thing:
| label |
|---|
| SKU-1: Nike Air Max 90 |
| SKU-2: adidas Ultraboost 22 |
| SKU-3: Puma RS-X |
Handy for building labels like a full display name from separate pieces.
Building a label
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:
| clean_name | brand |
|---|---|
| Nike Air Max 90 | Nike |
| adidas Ultraboost 22 | adidas |
| Puma RS-X | Puma |
Extracting 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 |
Run on our products:
| clean_name | name_length | no_space_model | max_starts_at |
|---|---|---|---|
| Nike Air Max 90 | 15 | Nike AirMax 90 | 12 |
| adidas Ultraboost 22 | 20 | adidas Ultraboost 22 | 0 |
| Puma RS-X | 9 | Puma RS-X | 0 |
Notice max_starts_at is 0 for rows with no "Max" in them: a 0 result means "not found", not an error.
LENGTH, REPLACE, and POSITION together
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? Zalora'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.