5 min read
Text Functions
Prerequisites: Choosing columns, aliases, and calculations
SQL has built-in functions for cleaning and formatting text. Most names are standard; a few differ between databases.
Example
SELECT
UPPER(name) AS upper_name,
LOWER(email) AS email,
LENGTH(name) AS name_length,
SUBSTR(name, 1, 3) AS first_three,
REPLACE(email, '@example.com', '') AS username
FROM customers
WHERE id <= 3;
Expected output
+----------------+-------------------+-------------+-------------+----------+
| upper_name | email | name_length | first_three | username |
+----------------+-------------------+-------------+-------------+----------+
| ADA LOVELACE | ada@example.com | 12 | Ada | ada |
| LINUS TORVALDS | linus@example.com | 14 | Lin | linus |
| GRACE HOPPER | grace@example.com | 12 | Gra | grace |
+----------------+-------------------+-------------+-------------+----------+
| Function | Does | Notes |
|---|---|---|
UPPER(s), LOWER(s) |
Change case | Standard |
LENGTH(s) |
Number of characters | LEN in SQL Server; CHAR_LENGTH in MySQL for characters |
SUBSTR(s, start, len) |
Part of a string (positions from 1) | SUBSTRING in PostgreSQL, MySQL, SQL Server |
TRIM(s) |
Remove surrounding spaces | Standard |
REPLACE(s, from, to) |
Replace text | Widely supported |
INSTR(s, find) |
Position of text, or 0 | POSITION(find IN s) in PostgreSQL |
Cleaning messy input
SELECT
TRIM(' Ada ') AS trimmed,
LOWER(TRIM(' Ada@Example.COM ')) AS normalized_email,
SUBSTR('ada@example.com', INSTR('ada@example.com', '@') + 1) AS domain;
+---------+------------------+-------------+
| trimmed | normalized_email | domain |
+---------+------------------+-------------+
| Ada | ada@example.com | example.com |
+---------+------------------+-------------+
Functions in WHERE
SELECT name
FROM customers
WHERE LENGTH(name) > 12;
+-------------------+
| name |
+-------------------+
| Linus Torvalds |
| Margaret Hamilton |
| Tim Berners-Lee |
+-------------------+
Common mistakes
- Assuming one database's function name works everywhere. When moving between databases, check the documentation — text functions are where dialects differ most.
- Applying a function to a column in
WHERE(likeLOWER(email) = ...) on large tables can prevent index use. Store data already normalized where you can.