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
ORDER BY emp_id
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 — in SQLite, which runs this playground, it does not — but lower-casing both sides makes the comparison behave the same everywhere:

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

Measuring length: LENGTH and LEN

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

Hiroshi and Isabela tie at seven characters and three names tie at six, so first_name breaks the ties and keeps the order the same on every run. Typical uses are data quality checks — finding suspiciously short names, or values that overflow a field limit.

The spelling is the annoying part. SQLite, MySQL and PostgreSQL say LENGTH, and this playground, which runs SQLite, accepts only that. SQL Server calls it LEN:

-- SQL Server
SELECT TOP 5 first_name, LEN(first_name) AS name_length
FROM Employees
ORDER BY name_length DESC, first_name;

The behaviour is not quite identical either. MySQL’s LENGTH counts bytes, so a letter such as é counts as two; CHAR_LENGTH counts characters there. SQL Server’s LEN ignores trailing spaces, so LEN('abc ') is 3, while SQLite’s LENGTH('abc ') is 5.

Slicing text: SUBSTRING

SELECT email, SUBSTRING(email, 1, 5) AS first_five
FROM Employees
ORDER BY emp_id
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
ORDER BY customer_id
LIMIT 5;

Find and replace: REPLACE

SELECT REPLACE(email, '@example.com', '') AS mailbox
FROM Employees
ORDER BY emp_id
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
ORDER BY emp_id
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 uses + or, since SQL Server 2012, CONCAT(). SQLite gained CONCAT() in version 3.44. The difference that bites is what happens when one part is NULL:

SELECT 'Ananya' || NULL AS with_pipes,
       CONCAT('Ananya', NULL) AS with_concat,
       'Ananya' || COALESCE(NULL, '') AS pipes_made_safe;

In this playground, as in PostgreSQL, a NULL anywhere in a || chain makes the whole result NULL, while CONCAT() skips it. That is not universal: MySQL’s CONCAT() returns NULL if any argument is NULL (its CONCAT_WS() skips them), SQL Server’s + returns NULL by default just like ||, and Oracle’s || treats NULL as empty text. The third column is the portable fix: wrap any part that may be missing in COALESCE(part, ''), and whichever way your database joins text, the answer is the same.

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
ORDER BY e.emp_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.

Practice this topic

Reading explains it; solving it is what makes it stick. These exercises use exactly what this page covered, and your answer is checked by running it against the same database:

• Mailing labels Easy
• Case-insensitive search Easy
• Strip the domain Medium
• The longest product names Medium
• Who reports to whom Medium

See all 42 exercises →

Frequently asked questions

Related reading

• SELECT DISTINCT in SQL: Removing Duplicate Rows from Results
• The SQL SELECT Statement: A Beginner’s Guide
• The SQL WHERE Clause: Filtering Data with Every Operator
• SQL Cheat Sheet: Core Syntax, Joins and Functions on One Page