SUBSTRING / TRIM

SUBSTRING: Extracts a substring starting at the given position with the given length.

SUBSTRING(string, start, length)
SUBSTR(string, start, length)   -- Shorthand alias

LEFT / RIGHT: Returns N characters from the left or right end.

LEFT(string, N)
RIGHT(string, N)

TRIM: Removes leading and trailing whitespace or a specified character.

TRIM(string)
TRIM(LEADING 'char' FROM string)   -- Remove from the beginning only
TRIM(TRAILING 'char' FROM string)  -- Remove from the end only
TRIM(BOTH 'char' FROM string)      -- Remove from both ends

LTRIM / RTRIM: Removes whitespace from the left or right end.

LTRIM(string)
RTRIM(string)

Function	Description
SUBSTRING(s, pos, len)	Position is 1-based. If len is omitted, returns the rest of the string.
LEFT(s, n)	Returns n characters from the left end of the string.
RIGHT(s, n)	Returns n characters from the right end of the string.
TRIM(s)	Removes leading and trailing whitespace from the string.
LTRIM(s)	Removes leading (left-side) whitespace from the string.
RTRIM(s)	Removes trailing (right-side) whitespace from the string.

The following examples use the employees table.

Extract the username (the part before @) from an email address.

sample_substring_trim.sql

SELECT
    email,
    SUBSTRING(
        email,
        1,
        POSITION('@' IN email) - 1
    ) AS username
FROM employees;

+--------------------------+--------------+
| email                    | username     |
+--------------------------+--------------+
| ayanami.rei@example.com  | ayanami.rei  |
| ikari.shinji@example.com | ikari.shinji |
| soryu.asuka@example.com  | soryu.asuka  |
+--------------------------+--------------+
3 rows in set

Extract the domain part (the part after @) from an email address.

sample_substring_trim.sql

SELECT
    email,
    RIGHT(
        email,
        CHAR_LENGTH(email) - POSITION('@' IN email)
    ) AS domain
FROM employees;

+--------------------------+-------------+
| email                    | domain      |
+--------------------------+-------------+
| ayanami.rei@example.com  | example.com |
| ikari.shinji@example.com | example.com |
| soryu.asuka@example.com  | example.com |
+--------------------------+-------------+
3 rows in set

Cleanse input data by trimming leading and trailing whitespace.

sample_substring_trim.sql

UPDATE employees
SET email = TRIM(email)
WHERE TRIM(email) <> email;

Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0

SELECT email FROM employees;

+--------------------------+
| email                    |
+--------------------------+
| ayanami.rei@example.com  |
| ikari.shinji@example.com |
| soryu.asuka@example.com  |
+--------------------------+
3 rows in set

Extract the first 3 characters of the local part (before @) from each email address.

sample_substring_trim.sql

SELECT
    email,
    LEFT(email, 3) AS local_prefix
FROM employees;

+--------------------------+--------------+
| email                    | local_prefix |
+--------------------------+--------------+
| ayanami.rei@example.com  | aya          |
| ikari.shinji@example.com | ika          |
| soryu.asuka@example.com  | sor          |
+--------------------------+--------------+
3 rows in set

SUBSTRING is available in MySQL, PostgreSQL, and SQL Server. Oracle uses SUBSTR. SQLite supports both SUBSTR and SUBSTRING.

-- Oracle / SQLite: Use SUBSTR instead.
SELECT SUBSTR(email, 1, INSTR(email, '@') - 1) AS username
FROM employees;

LEFT and RIGHT are available in MySQL, SQL Server, and PostgreSQL. In Oracle and SQLite, use SUBSTR as an alternative.

-- MySQL / PostgreSQL
SELECT email, LEFT(email, 3) AS local_prefix FROM employees;

-- SQLite (LEFT not available; use SUBSTR)
SELECT email, SUBSTR(email, 1, 3) AS local_prefix FROM employees;

TRIM is available across all databases. LTRIM and RTRIM are also consistent across MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

The start position in SUBSTRING is 1-based (unlike array indexes, which start at 0). Some DBMSs treat a start position of 0 as equivalent to 1, but it is best practice to always specify 1 explicitly.

TRIM removes whitespace (space characters) from both ends by default, but you can also specify a character to remove. Full-width spaces are not removed by TRIM. To remove full-width spaces, use REPLACE to substitute them with an empty string explicitly.

For string concatenation and length, see CONCAT / LENGTH. For string replacement and case conversion, see UPPER / LOWER / REPLACE.