String Functions (LENGTH, UPPER, LOWER, TRIM, SUBSTRING) in SQL
`LENGTH`, `UPPER`, `LOWER`, `TRIM`, and `SUBSTRING` are scalar string functions that inspect or transform a single text value and return a single result. Each operates on one row at a time, producing one output value per input row.
LENGTH, UPPER, LOWER, TRIM, and SUBSTRING are the core tools for measuring, normalizing, and extracting pieces of string values.
String data in analytical work is rarely clean. Customer names arrive with inconsistent casing. Product codes have leading zeros or trailing spaces. Dates are embedded inside longer strings and need to be sliced out. These five functions handle the most common cleaning and extraction tasks, one row at a time — each operates on one value and returns one result. Nothing is grouped or aggregated.
LENGTH counts characters. UPPER and LOWER convert case. TRIM removes characters from the edges. SUBSTRING extracts a slice starting at a given position. Here's what they look like on real data:
TRIM deserves a closer look. By default it removes spaces from both ends. You can also specify which characters to strip and which side to trim from:
TRIM(' hello ') -- 'hello'
TRIM(LEADING '0' FROM '007') -- '7'
LTRIM('###note', '#') -- 'note'The characters argument specifies a set of characters to remove, not a prefix or substring. TRIM('xy' FROM 'xyhelloyx') strips any leading or trailing x or y characters — one by one, until it hits a character not in the set.
SUBSTRING extracts a slice by position. Positions are 1-indexed: the first character is position 1.
SUBSTRING('order-2024-03-15', 7, 10) -- '2024-03-15'
SUBSTRING('SKU-4821-B', 5, 4) -- '4821'The first argument is the string, the second is the start position, the third is the number of characters to return. Omit the length and SUBSTRING returns everything from the start position to the end of the string.
A few edge cases worth knowing: requesting a SUBSTRING start position beyond the end of the string returns an empty string, not an error or NULL. LENGTH('') returns 0. UPPER('') returns ''. The functions handle empty strings gracefully without any special handling needed.
The one thing that trips people up: all five functions return NULL when the input is NULL.
LENGTH(NULL) is NULL, not 0. TRIM(NULL) is NULL, not an empty string. If a column can contain NULLs and you need a non-NULL result, wrap with COALESCE: LENGTH(COALESCE(name, '')) returns 0 for NULL names instead of NULL. This pattern applies to all five functions — always consider whether a NULL input would produce a NULL output that would silently flow through the rest of your query.
You run TRIM('ab' FROM 'ababHELLOabab'). What does PostgreSQL return?
9 String Functions (LENGTH, UPPER, LOWER, TRIM, SUBSTRING) practice problems
Write a query to return the uppercase version of the string `'Wireless Keyboard'`.
Write a query to return the character count of the product code `'SKU-4821-B-XL'`.
Write a query to return the trimmed value of the string `' [email protected] '`.
Write a query to return the `10`-character segment starting at position `5` of the order code `'ORD-2024-03-15'`.
Write a query to return the cleaned version of the name `' Owen Marshall '`.
Write a query to return the value of `'000042'` after every leading `'0'` character is removed.
Write a query to return everything from position `9` onward in the string `'ERROR: Connection refused'`.
Write a query to return the string with every leading and trailing `'a'` or `'b'` character removed.
Write a query to return the character count of the string `'Alexandra'` and the character count of a missing value (a SQL `NULL`) in a single row.
These problems are part of the String Functions (LENGTH, UPPER, LOWER, TRIM, SUBSTRING) lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.