String Functions

Text data is rarely in exactly the shape you need. String functions change case, measure length, extract parts, trim spaces, find and replace — essential for cleaning data, building labels and searching. Function names vary more between databases here than anywhere else, so the table at the end maps the common ones.

Changing case

SELECT name, upper(name) AS shouting, lower(name) AS quiet
FROM products
WHERE id IN (1, 4);

A common use is case-insensitive comparison: WHERE lower(email) = lower('Ana.Pereira@Example.com'). (SQLite's built-in upper/lower only convert ASCII letters; other databases handle accented letters too.)

Length

SELECT name, length(name) AS characters
FROM products
ORDER BY characters DESC
LIMIT 3;

length counts characters in SQLite, PostgreSQL and Oracle. In MySQL, LENGTH counts bytes and CHAR_LENGTH counts characters; SQL Server uses LEN.

Extracting part of a string

SELECT email,
       substr(email, 1, instr(email, '@') - 1) AS username,
       substr(email, instr(email, '@') + 1) AS domain
FROM customers
WHERE id <= 3;

substr(text, start, length) extracts characters; positions start at 1, not 0. Leave out the length to take everything to the end. instr(text, find) returns the position of the first match, or 0. (Standard SQL spells these SUBSTRING and POSITION(find IN text).)

SELECT first_name, substr(first_name, 1, 1) || '. ' || last_name AS short_name
FROM employees
WHERE department_id = 4;

Trimming and padding

SELECT '[' || trim('   spaced out   ') || ']' AS trimmed,
       '[' || ltrim('   left') || ']' AS left_trimmed,
       trim('--note--', '-') AS custom;

trim removes spaces (or the characters you list) from both ends — useful when cleaning imported data. For padding, MySQL, PostgreSQL and Oracle have LPAD and RPAD; in SQLite, printf / format does the job:

SELECT format('%05d', id) AS order_ref, format('[%-10s]', status) AS padded
FROM orders
WHERE id IN (3, 12);

Replacing text

SELECT name, replace(name, ' ', '-') AS slug
FROM products
WHERE category = 'Home';

SELECT lower(replace(replace(name, ' ', '-'), '.', '')) AS url_slug
FROM products
WHERE id = 2;

Functions nest: the inner call's result becomes the outer call's input.

Joining strings

SELECT concat_ws(', ', name, city, country) AS address_line
FROM customers
WHERE id IN (1, 6);

concat_ws (concatenate with separator) puts the separator between values and skips NULLs — notice Farah's line has no gap for the missing city. It's available in SQLite, PostgreSQL, MySQL and SQL Server.

Common functions across databases

Try it yourself

Build a staff directory line like "WEBER, Jonas — Senior Developer" for every employee.