String Functions (LENGTH, UPPER, LOWER, TRIM, SUBSTRING) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Literal Values, Data Types, and Type Casting
Builds toward String Concatenation and Formatting, Pattern Matching (LIKE, ILIKE, SIMILAR TO, Regex)
What are String Functions in SQL?
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.
What do LENGTH, UPPER, LOWER, TRIM and SUBSTRING do?
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:
SELECT name, LENGTH(name) AS name_length, UPPER(name) AS name_upper, LOWER(name) AS name_lower, TRIM(name) AS name_trimmed FROM customers LIMIT 5
How do you strip specific characters with TRIM?
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.
How do you extract part of a string in PostgreSQL?
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.
What does LENGTH(NULL) return in SQL?
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?
Practice String Functions in SQL
Brightlane's customer data normalization pipeline standardizes product names to uppercase for case-insensitive matching.
Write a query to return the uppercase version of the string 'Wireless Keyboard'.
Output:
- A single row with one column,
normalized_name, containing the uppercased string.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 String Functions 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.
Start learning to practice all 9 String Functions problems, with instant grading and mastery tracking.
Common questions about String Functions
Does LENGTH count characters or bytes?
Characters. A four-letter word with an accent returns four, even though it occupies five bytes of storage. Reach for OCTET_LENGTH when the byte count is what you actually need, which is usually a storage question rather than an analysis one.
How do you trim only the leading spaces?
Say which end you mean with LEADING or TRAILING. Plain TRIM removes from both, so it is the wrong tool when the padding on one side is meaningful, as it is in fixed-width data pulled out of an export.
What does SUBSTRING return if it starts past the end of the string?
An empty string, not NULL and not an error. That matters when the result feeds a comparison, because an empty string is a value that compares normally while a NULL would quietly drop the row.
