SQL String Functions: UPPER, SUBSTRING, TRIM & More

Text arrives messy: inconsistent capitalisation, stray spaces, values glued together that you need apart, values apart that you need together. String functions are SQL's toolkit for cleaning and reshaping text inside the query, so the data comes back the way the report needs it. This guide covers the core set that exists, under one spelling or another, in every major database.

Changing case: UPPER and LOWER

SELECT UPPER(first_name) AS shouted, LOWER(email) AS normalised
FROM Employees
LIMIT 3;

Cosmetics are the obvious use; the important one is case-insensitive comparison. Whether 'Austin' equals 'austin' depends on the database's collation settings — but lower-casing both sides makes the comparison behave the same everywhere:

SELECT company, city
FROM Customers
WHERE LOWER(city) = 'austin';

Measuring length: LEN and LENGTH

SELECT first_name, LEN(first_name) AS name_length
FROM Employees
ORDER BY name_length DESC
LIMIT 5;

The spelling is the annoying part: SQL Server and this playground's engine use LEN, while MySQL, PostgreSQL and SQLite use LENGTH. The behaviour is the same. Typical uses are data quality checks — finding suspiciously short names, or values that overflow a field limit.

Slicing text: SUBSTRING

SELECT email, SUBSTRING(email, 1, 5) AS first_five
FROM Employees
LIMIT 3;

The arguments are the string, the starting position, and how many characters to take — and SQL counts from 1, not 0, which surprises everyone arriving from a programming language. Combined with other functions it builds things like initials:

SELECT company, UPPER(SUBSTRING(company, 1, 1)) AS initial
FROM Customers
LIMIT 5;

Find and replace: REPLACE

SELECT REPLACE(email, '@example.com', '') AS mailbox
FROM Employees
LIMIT 4;

REPLACE swaps every occurrence of one substring for another — here replacing the domain with nothing, which strips it. It only changes the query's output; the stored data is untouched unless you put the expression inside an UPDATE.

Trimming whitespace: TRIM

SELECT TRIM('   SQL Practice   ') AS trimmed;

TRIM removes leading and trailing spaces — the classic cure for data pasted in from spreadsheets, where 'Sales ' quietly fails to equal 'Sales'. Most databases also offer LTRIM and RTRIM for one side only.

Joining text: || and CONCAT

SELECT first_name || ' ' || last_name AS full_name,
       CONCAT(last_name, ', ', first_name) AS sorted_name
FROM Employees
LIMIT 3;

Both forms work here, which is convenient because the real world splits: || is the SQL standard (PostgreSQL, SQLite, Oracle), while MySQL wants CONCAT() and SQL Server historically used +. One behavioural difference worth knowing: with ||, a NULL anywhere makes the whole result NULL, whereas CONCAT in most databases treats NULL as an empty string.

Handling missing text: COALESCE and NULLIF

Not strictly string functions — they work on any type — but text is where you meet them daily. COALESCE returns the first non-NULL argument, which is how a NULL becomes a readable label:

SELECT e.first_name, COALESCE(m.first_name, 'no manager') AS reports_to
FROM Employees e
LEFT JOIN Employees m ON m.emp_id = e.manager_id
LIMIT 6;

NULLIF is its mirror: NULLIF(a, b) returns NULL when the two arguments are equal, otherwise returns the first. Its everyday job is turning junk placeholder values — empty strings, 'N/A' — back into honest NULLs before COALESCE or aggregates handle them:

SELECT NULLIF('same', 'same') AS becomes_null, NULLIF('a', 'b') AS stays_a;

One habit worth building

Wrapping a column in a function inside WHERE — as in LOWER(city) = 'austin' — is fine on small data, but on a large indexed table it can stop the database using the index, because the index stores city, not LOWER(city). Production databases solve this with case-insensitive collations or function-based indexes. Nothing to act on while learning — just remember that where you put the function matters more as tables grow.

Frequently asked questions

Related reading

• The SQL SELECT Statement: A Beginner’s Guide
• The SQL WHERE Clause: Filtering Data with Every Operator
• SQL Cheat Sheet: Every Clause and Function on One Page