(opens in a new tab)

SQL String Functions: Complete Reference with Examples

August 14, 2026 by Chat2DB Team

String functions in SQL do the unglamorous work of real databases: normalizing imported data, splitting composite fields, padding identifiers, and matching messy user input. As with date functions, the SQL standard covers only a fraction of what you need, and MySQL, PostgreSQL, and SQL Server each fill the gaps differently — sometimes with the same function name behaving differently.

This reference walks through every commonly used string function with runnable examples, flags the cross-dialect traps, and ends with practical data-cleaning recipes.

All examples run against this table (valid on all three engines):

CREATE TABLE users_raw (
    user_id    INT PRIMARY KEY,
    full_name  VARCHAR(100),
    email      VARCHAR(255),
    phone      VARCHAR(50)
);
 
INSERT INTO users_raw (user_id, full_name, email, phone) VALUES
(1, '  Grace Hopper ',   'GRACE.HOPPER@Example.COM', '+1 (555) 010-7788'),
(2, 'ada lovelace',      'ada@example.com',          '555 010 2244'),
(3, 'Linus  Torvalds',   'linus@kernel.example.org', NULL),
(4, 'MARGARET HAMILTON', 'm.hamilton@nasa.example',  '(555)0109911');

Concatenation: CONCAT and ||

-- Works on MySQL, PostgreSQL, SQL Server (2012+)
SELECT CONCAT(full_name, ' <', email, '>') AS mailbox
FROM users_raw WHERE user_id = 2;
-- => 'ada lovelace <ada@example.com>'
 
-- PostgreSQL and standard SQL: the || operator
SELECT full_name || ' <' || email || '>' FROM users_raw WHERE user_id = 2;

Trap: NULL handling differs. CONCAT treats NULL as an empty string on all three engines, but the || operator propagates NULL in PostgreSQL (any NULL operand makes the whole result NULL). In MySQL, || is a logical OR by default unless PIPES_AS_CONCAT mode is set. SQL Server's + operator also propagates NULL unless CONCAT_NULL_YIELDS_NULL is off. For joining values with separators and skipping NULLs, CONCAT_WS(', ', a, b, c) exists on all three (SQL Server 2017+).

SUBSTRING, LEFT and RIGHT

-- All three engines; positions are 1-based
SELECT SUBSTRING(email, 1, 5) FROM users_raw WHERE user_id = 2;
-- => 'ada@e'
 
SELECT LEFT(email, 3), RIGHT(email, 4)
FROM users_raw WHERE user_id = 2;
-- => 'ada', '.com'

MySQL and PostgreSQL also accept SUBSTRING(email FROM 1 FOR 5) (the standard form), and MySQL offers SUBSTR as a synonym. SQL Server requires all three arguments — SUBSTRING(email, 5) without a length is an error there, while MySQL and PostgreSQL return the remainder of the string. PostgreSQL gained LEFT/RIGHT long ago, so all three support them; negative lengths in PostgreSQL's LEFT(str, -2) mean "all but the last 2 characters."

Length: LENGTH, LEN and CHAR_LENGTH

-- MySQL: LENGTH is bytes, CHAR_LENGTH is characters
SELECT LENGTH('naïve'), CHAR_LENGTH('naïve');   -- => 6, 5 (utf8)
 
-- PostgreSQL: LENGTH and CHAR_LENGTH are characters, OCTET_LENGTH is bytes
SELECT LENGTH('naïve'), OCTET_LENGTH('naïve');  -- => 5, 6
 
-- SQL Server: LEN (ignores trailing spaces!), DATALENGTH is bytes
SELECT LEN('abc  '), DATALENGTH(N'naïve');      -- => 3, 10

Two traps here: MySQL's LENGTH counts bytes, not characters — use CHAR_LENGTH for multibyte-safe counts — and SQL Server's LEN silently ignores trailing spaces, so LEN('abc ') is 3, not 5.

Case: UPPER, LOWER, INITCAP

SELECT UPPER(full_name), LOWER(email) FROM users_raw WHERE user_id = 1;
-- => '  GRACE HOPPER ', 'grace.hopper@example.com'

UPPER and LOWER are universal. Proper-casing is not: PostgreSQL has INITCAP('ada lovelace') → 'Ada Lovelace'; MySQL and SQL Server have no built-in equivalent and typically combine UPPER(LEFT(...)) with LOWER(SUBSTRING(...)) or a user-defined function.

Trimming: TRIM, LTRIM, RTRIM

-- All three engines
SELECT TRIM(full_name) FROM users_raw WHERE user_id = 1;
-- => 'Grace Hopper'
 
-- Trim specific characters (MySQL, PostgreSQL; SQL Server 2017+)
SELECT TRIM(BOTH '+' FROM '+1555+');   -- => '1555'

LTRIM/RTRIM strip only leading/trailing spaces on MySQL and older SQL Server; PostgreSQL and SQL Server 2022+ accept a second argument listing characters to remove: RTRIM('report.csv', '.csv') — but beware, that argument is a character set, not a suffix string, so it removes any trailing c, s, v, or . characters.

REPLACE

SELECT REPLACE(phone, ' ', '') FROM users_raw WHERE user_id = 2;
-- => '5550102244'

REPLACE(str, from, to) is identical on all three engines: it replaces every occurrence, is case-sensitive on PostgreSQL and (usually) case-insensitive on SQL Server/MySQL depending on collation. Chained REPLACE calls are the standard idiom for stripping several characters:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(phone, ' ', ''), '(', ''), ')', ''), '-', '')
FROM users_raw WHERE user_id = 1;
-- => '+15550107788'

Finding Substrings: POSITION, LOCATE, CHARINDEX

Each engine has its own primary spelling, all returning a 1-based index and 0 when not found:

-- Standard / PostgreSQL / MySQL
SELECT POSITION('@' IN email) FROM users_raw WHERE user_id = 2;   -- => 4
 
-- MySQL: LOCATE(needle, haystack [, start])
SELECT LOCATE('@', email) FROM users_raw WHERE user_id = 2;       -- => 4
 
-- SQL Server: CHARINDEX(needle, haystack [, start])
SELECT CHARINDEX('@', email) FROM users_raw WHERE user_id = 2;    -- => 4
 
-- PostgreSQL also: STRPOS(haystack, needle)
SELECT STRPOS(email, '@') FROM users_raw WHERE user_id = 2;       -- => 4

Note the argument-order hazard: LOCATE and CHARINDEX take the needle first, STRPOS takes the haystack first. Combining position with SUBSTRING splits a string at a delimiter:

-- Extract the domain from an email (all three engines)
SELECT SUBSTRING(email, POSITION('@' IN email) + 1, 255) AS domain
FROM users_raw WHERE user_id = 3;
-- => 'kernel.example.org'
-- SQL Server: SUBSTRING(email, CHARINDEX('@', email) + 1, 255)

Padding: LPAD and RPAD

-- MySQL and PostgreSQL
SELECT LPAD(CAST(user_id AS CHAR(6)), 6, '0') FROM users_raw WHERE user_id = 3;
-- => '000003'

SQL Server has no LPAD; the idiom is RIGHT(REPLICATE('0', 6) + CAST(user_id AS VARCHAR(6)), 6). Note that MySQL's LPAD truncates strings longer than the target length (LPAD('abcdef', 4, '0') → 'abcd'), which surprises people expecting a no-op.

Splitting: SPLIT_PART and STRING_SPLIT

-- PostgreSQL: SPLIT_PART(string, delimiter, n)
SELECT SPLIT_PART('kernel.example.org', '.', 1);   -- => 'kernel'
 
-- MySQL: SUBSTRING_INDEX(string, delimiter, n) returns everything up to the nth delimiter
SELECT SUBSTRING_INDEX('kernel.example.org', '.', 1);    -- => 'kernel'
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('kernel.example.org', '.', 2), '.', -1);  -- => 'example'
 
-- SQL Server 2016+: STRING_SPLIT returns a table
SELECT value FROM STRING_SPLIT('kernel.example.org', '.');
-- => three rows: 'kernel', 'example', 'org'

STRING_SPLIT did not guarantee output order until SQL Server 2022 added the enable_ordinal argument; before that, extracting "the second part" reliably required OPENJSON tricks. The reverse operation — aggregating rows into one delimited string — is GROUP_CONCAT(col SEPARATOR ', ') in MySQL, STRING_AGG(col, ', ') in PostgreSQL and SQL Server 2017+.

Regular Expressions

-- MySQL 8.0+
SELECT email FROM users_raw
WHERE REGEXP_LIKE(email, '^[a-z0-9._]+@[a-z0-9.-]+$');            -- match
SELECT REGEXP_REPLACE(phone, '[^0-9+]', '') FROM users_raw;       -- keep digits and +
SELECT REGEXP_SUBSTR(email, '[^@]+') FROM users_raw;              -- part before @
 
-- PostgreSQL
SELECT email FROM users_raw WHERE email ~* '^[a-z0-9._]+@[a-z0-9.-]+$';  -- ~* = case-insensitive
SELECT REGEXP_REPLACE(phone, '[^0-9+]', '', 'g') FROM users_raw;         -- 'g' = all matches!
SELECT (REGEXP_MATCH(email, '^([^@]+)'))[1] FROM users_raw;
 
-- SQL Server: no real regex before 2025; LIKE and PATINDEX only
SELECT email FROM users_raw WHERE email LIKE '%[^a-z0-9.@_]%';    -- has an invalid char
SELECT PATINDEX('%[0-9]%', phone) FROM users_raw;                 -- position of first digit

The critical PostgreSQL trap: REGEXP_REPLACE replaces only the first match unless you pass the 'g' flag, whereas MySQL replaces all matches by default. SQL Server historically had no regex support at all — LIKE/PATINDEX with character classes are the workaround, and REGEXP_LIKE only arrives in SQL Server 2025.

Practical Data Cleaning

Putting it together — normalize the messy sample data into a clean result:

-- PostgreSQL version
SELECT
    user_id,
    INITCAP(REGEXP_REPLACE(TRIM(full_name), '\s+', ' ', 'g')) AS clean_name,
    LOWER(TRIM(email))                                        AS clean_email,
    NULLIF(REGEXP_REPLACE(COALESCE(phone, ''), '[^0-9+]', '', 'g'), '') AS clean_phone
FROM users_raw
ORDER BY user_id;

Expected output:

The pattern is always the same three layers: TRIM the edges, collapse or strip unwanted characters (REPLACE or REGEXP_REPLACE), then normalize case (LOWER/UPPER/INITCAP). Wrapping the result in NULLIF(..., '') converts empty strings back to NULL so that downstream IS NULL checks keep working.

A second everyday recipe — validating and deduplicating emails case-insensitively:

SELECT LOWER(TRIM(email)) AS normalized_email, COUNT(*) AS occurrences
FROM users_raw
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

On our sample data this returns zero rows, but run against a real import table it surfaces duplicates that a case-sensitive unique constraint would miss. When iterating on cleaning queries like these, an editor with inline result grids — for example Chat2DB (opens in a new tab) — makes it quick to eyeball before/after values column by column while you refine each expression.

Quick Reference Table

FAQ

Are SQL string positions 0-based or 1-based? 1-based everywhere: SUBSTRING(s, 1, 3) returns the first three characters, and position functions return 0 (not -1) when the needle is absent.

Why does LENGTH give a different number than expected? You are probably counting bytes of a multibyte string (MySQL LENGTH) or hitting SQL Server's LEN trailing-space rule. Use CHAR_LENGTH/DATALENGTH to disambiguate.

Do string functions in WHERE clauses use indexes? Wrapping an indexed column in a function (WHERE LOWER(email) = ...) defeats a plain B-tree index. Fix it with a functional/computed-column index on LOWER(email), a case-insensitive collation, or PostgreSQL's citext type.

Which functions are safe to use portably? CONCAT, SUBSTRING(s, start, len), UPPER, LOWER, TRIM, REPLACE, LEFT, RIGHT behave near-identically on all three engines. Everything involving byte lengths, padding, splitting, or regex needs a dialect check.