SQL String Functions: UPPER, LOWER, LENGTH, SUBSTRING, TRIM, REPLACE, CONCAT

Text data is rarely perfect. Names arrive in random capitalisation, emails contain the domain you want to group by, and codes have stray spaces. String functions let you clean, reshape and inspect text directly in your queries.

The example table#

Quick reference#

The examples below use the forms that work in PostgreSQL and SQLite; check the table for your database.

UPPER, LOWER and LENGTH#

SELECT name,
       UPPER(name)  AS upper_name,
       LOWER(name)  AS lower_name,
       LENGTH(name) AS len
FROM employees
WHERE id <= 3;

name  | upper_name | lower_name | len
------+------------+------------+----
Asha  | ASHA       | asha       | 4
Ravi  | RAVI       | ravi       | 4
Meera | MEERA      | meera      | 5

LOWER is the classic tool for case-insensitive search: WHERE LOWER(email) = LOWER('Asha@Company.com').

Joining strings (concatenation)#

SELECT name || ' (' || city || ')' AS label
FROM employees
WHERE id IN (1, 5);

label
-------------
Asha (Mumbai)
Priya (Delhi)

In MySQL write CONCAT(name, ' (', city, ')'); || means OR there by default.

Extracting part of a string: SUBSTRING#

SUBSTR(text, start, length) returns length characters starting at position start. Positions start at 1, not 0:

SELECT name, SUBSTR(name, 1, 3) AS short
FROM employees
WHERE id <= 3;

name  | short
------+------
Asha  | Ash
Ravi  | Rav
Meera | Mee

Leave out the length to take everything to the end of the string.

Finding a position#

Combine a position function with SUBSTR to split text at a character. The domain of each email:

SELECT email,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM employees
WHERE email IS NOT NULL;

email             | domain
------------------+------------
asha@company.com  | company.com
ravi@company.com  | company.com
meera@company.com | company.com
priya@company.com | company.com

INSTR(email, '@') finds where the @ is (5 for asha@...), and SUBSTR takes everything after it. PostgreSQL also offers SPLIT_PART(email, '@', 2), and MySQL SUBSTRING_INDEX(email, '@', -1).

TRIM: remove extra spaces#

Imported data often has leading or trailing spaces that make 'Pune ' different from 'Pune':

SELECT TRIM('   hello   ') AS trimmed,
       LENGTH(TRIM('   hello   ')) AS len;

trimmed | len
--------+----
hello   | 5

LTRIM and RTRIM remove spaces from only the left or right side.

REPLACE#

REPLACE(text, find, replace_with) swaps every occurrence:

SELECT name, REPLACE(category, 'Electronics', 'Tech') AS category
FROM products
WHERE category = 'Electronics';

name       | category
-----------+---------
Laptop     | Tech
Headphones | Tech
Monitor    | Tech

Combined with UPDATE, it can clean a whole column: UPDATE customers SET phone = REPLACE(phone, ' ', '');

Nesting functions#

Functions can be nested, innermost first. Fixing the capitalisation of a name:

SELECT UPPER(SUBSTR('aSHA', 1, 1)) || LOWER(SUBSTR('aSHA', 2)) AS fixed;

fixed
-----
Asha

PostgreSQL and Oracle have a built-in INITCAP('aSHA') for this.

Common mistakes#

Practice#

SELECT name,
       email,
       SUBSTR(email, 1, INSTR(email, '@') - 1) AS username
FROM employees
WHERE email IS NOT NULL;

name  | email             | username
------+-------------------+---------
Asha  | asha@company.com  | asha
Ravi  | ravi@company.com  | ravi
Meera | meera@company.com | meera
Priya | priya@company.com | priya

Key takeaway#