~/learn/SQL · Dates & text
String functions
Concatenate, slice, clean and search text columns.
why this matters
Real data is messy — mixed capitals, extra spaces, codes buried in strings. Text functions tidy it up.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
|| glues text together, and UPPER, LOWER and LENGTH do exactly what they say. These show up constantly when you build labels or keys.
SUBSTR cuts a slice out of a string and INSTR finds a character's position — combine them to split a full name into first and last.
Try it: Return just the first initial followed by a dot.
TRIM removes surrounding whitespace and REPLACE swaps substrings — the two workhorses of cleaning messy imports. LIKE and its wildcards handle pattern search.
Note
Wrapping a column in a function stops an index on that column from being used. Filter on the raw column when you can, and store a cleaned copy when you cannot.
key takeaways
- LOWER/UPPER normalise case.
- TRIM removes spaces.
- SUBSTR and INSTR slice text; || joins it.
check your understanding
Which joins first and last name with a space in SQLite?
free practice
Build confidence with a question you can solve without Pro.