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)

-- Extract the username (the part before @) from an email address.
SELECT
    email,
    SUBSTRING(
        email,
        1,
        POSITION('@' IN email) - 1
    ) AS username
FROM employees;

-- Extract the first 3 digits (area code) from a postal code.
SELECT
    customer_name,
    postal_code,
    LEFT(postal_code, 3) AS area_code
FROM customers;

-- Cleanse input data by trimming leading and trailing whitespace.
UPDATE customers
SET customer_name = TRIM(customer_name),
    email = TRIM(email)
WHERE TRIM(customer_name) <> customer_name
   OR TRIM(email) <> email;

-- Parse a product code where the first 2 characters indicate the category.
SELECT
    product_code,
    product_name,
    LEFT(product_code, 2) AS category_code,
    RIGHT(product_code, 4) AS sequence_number
FROM products;

-- Extracting usernames from email addresses.
email                   | username
------------------------+-----------
yamada@example.com      | yamada
suzuki.h@example.com    | suzuki.h
sato123@example.com     | sato123

-- Parsing product codes.
product_code | product_name        | category_code | sequence_number
-------------+---------------------+---------------+----------------
EL0042       | Bluetooth Speaker   | EL            | 0042
KT0018       | Electric Kettle     | KT            | 0018

『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.

-- Oracle: Use SUBSTR as a substitute for LEFT(postal_code, 3).
SELECT customer_name, SUBSTR(postal_code, 1, 3) AS area_code FROM customers;

-- Oracle: Use SUBSTR as a substitute for RIGHT(product_code, 4).
SELECT product_code, SUBSTR(product_code, -4) AS sequence_number FROM products;

『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』.