String Manipulation Functions in SQL (With Examples)

The basics: SUBSTRING, TRIM, LENGTH

SELECT
  SUBSTRING(product_code, 1, 3)     AS category_prefix,  -- first 3 characters
  TRIM(customer_name)              AS trimmed_name,
  LENGTH(sku)                      AS sku_length,       -- PostgreSQL/MySQL
  LEN(sku)                         AS sku_length_mssql -- SQL Server uses LEN, not LENGTH
FROM products;

SUBSTRING is one of the few functions with genuinely identical syntax across all three engines: SUBSTRING(string, start, length). LENGTH() vs. LEN() is the first real naming split — SQL Server is the odd one out.

Joining strings: CONCAT vs. ||

-- Works in all three:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

-- PostgreSQL / Oracle only:
SELECT first_name || ' ' || last_name AS full_name FROM customers;

Finding a position: POSITION vs. CHARINDEX

-- PostgreSQL / MySQL: find where '@' sits in an email address
SELECT POSITION('@' IN email) AS at_position FROM customers;

-- SQL Server equivalent
SELECT CHARINDEX('@', email) AS at_position FROM customers;

Both return a 1-based index, and both return 0 (not NULL) when the character isn't found — worth checking for explicitly if a query depends on the character actually being present.

Splitting on a delimiter

Combining position-finding with SUBSTRING extracts text on either side of a delimiter without a native "split" function:

-- Everything before the '@' (the username portion of an email)
SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM customers;

-- PostgreSQL also offers SPLIT_PART() directly for this exact case:
SELECT SPLIT_PART(email, '@', 1) AS username FROM customers;

Pattern matching with regex functions

Regex functions handle patterns a fixed SUBSTRING/POSITION combination can't — variable-length matches, optional characters, multiple valid formats:

-- PostgreSQL / MySQL 8.0+: strip everything that isn't a digit from a phone number
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits_only
FROM customers;

-- Check whether a value matches an expected email shape
SELECT * FROM customers
WHERE email !~ '^[^@]+@[^@]+\.[a-z]+$';  -- PostgreSQL's NOT-match operator

A practical example: parsing an email column

Pulling out both the username and the domain from a single email column, ready to use in a report:

SELECT
  email,
  SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username,
  SUBSTRING(email, POSITION('@' IN email) + 1, LENGTH(email)) AS domain
FROM customers
WHERE email IS NOT NULL;

Grouping by domain afterward is a quick way to spot a suspiciously large share of disposable or typo'd email domains in a signup dataset.

Common mistakes

• Assuming LENGTH() works in SQL Server — it's LEN() there, and the function names diverge in several other places too.
• Using || in a query where any joined column can be NULL, silently collapsing the whole result to NULL — CONCAT() avoids this.
• Forgetting POSITION()/CHARINDEX() returns 0, not NULL, when nothing matches — a downstream SUBSTRING call with a negative or zero length can then error or return an unexpected result.
• Reaching for a regex function in SQL Server without checking it's actually available — it isn't, natively.