SQL String Functions Explained: TRIM, SUBSTR, REPLACE, CONCAT and Data Cleanup

A report counts customers by city and shows “pune: 1” and “Pune : 1” as separate rows. A mailing list has emails with capital letters and trailing spaces, so duplicates slip through. A full-name column is blank for anyone without a surname. All three problems are solved with SQL string functions, the everyday tools for cleaning and shaping text.

We ran every query in SQLite 3.50, and the results are from that run. Most functions exist in every database, sometimes with different names, listed in the cheat sheet below.

The messy sample data

CREATE TABLE customers (id INTEGER PRIMARY KEY, first TEXT, last TEXT,
                        email TEXT, phone TEXT, city TEXT);
INSERT INTO customers (first, last, email, phone, city) VALUES
 ('  Asha ', 'Rao',  'Asha.Rao@Example.com ', '+91 98765-43210',  'pune'),
 ('Ben',     NULL,   'ben@example.com',       '(022) 2345 6789',  'MUMBAI'),
 ('Chitra',  'Iyer', 'chitra@shop.co.in',     '9876543210',       'Pune ');

Extra spaces, inconsistent capitals, a missing surname and three different phone formats: a fairly typical import from a web form or spreadsheet.

Concatenation and the NULL trap

SELECT first || ' ' || last FROM customers;
-- '  Asha  Rao', NULL, 'Chitra Iyer'

Two problems at once. Asha’s name kept its stray spaces, and Ben’s full name disappeared entirely, because in standard SQL anything concatenated with NULL becomes NULL. Two fixes:

SELECT trim(first) || COALESCE(' ' || last, '') FROM customers;
-- 'Asha Rao', 'Ben', 'Chitra Iyer'

SELECT concat_ws(' ', trim(first), last) FROM customers;
-- 'Asha Rao', 'Ben', 'Chitra Iyer'

COALESCE replaces the NULL piece with an empty string, putting the space inside the optional part so Ben does not get a trailing space. CONCAT_WS, “concatenate with separator”, skips NULLs automatically and is available in SQLite 3.44 and later, MySQL, PostgreSQL and SQL Server 2017+. Note that MySQL’s plain CONCAT() returns NULL if any argument is NULL, while PostgreSQL’s and SQL Server’s CONCAT() treat NULL as empty. Our guide to NULL values covers more of these surprises.

TRIM and LENGTH

SELECT first, length(first), length(trim(first)) FROM customers WHERE id = 1;
-- '  Asha ', 7, 4

Three invisible characters were hiding in “Asha”. TRIM removes leading and trailing spaces; LTRIM and RTRIM remove one side only. SQLite and several other databases let you name the characters to remove, such as trim(code, '.-'). Note that TRIM does not remove spaces in the middle; that is a job for REPLACE. Tabs and non-breaking spaces copied from web pages are also not removed by a plain TRIM in most databases, a common reason “cleaned” values still do not match.

UPPER and LOWER

SELECT lower(trim(email)) FROM customers;
-- 'asha.rao@example.com', 'ben@example.com', 'chitra@shop.co.in'

SELECT upper(substr(trim(city), 1, 1)) || lower(substr(trim(city), 2)) FROM customers;
-- 'Pune', 'Mumbai', 'Pune'

Normalising emails to lower case and trimming them is the standard first step before checking for duplicates or joining tables. The second query capitalises the first letter of each city, a simple title case for single words. One catch: SQLite’s built-in lower() only changes ASCII letters. lower('ÇAFÉ') returned 'ÇafÉ', leaving the accented capitals untouched. Other databases handle Unicode case better, depending on collation and settings.

SUBSTR and INSTR

SELECT substr(email, instr(email, '@') + 1) FROM customers;
-- 'Example.com ', 'example.com', 'shop.co.in'

SELECT substr('INV-2026-0042', -4);
-- '0042'

INSTR finds the position of the “@”, and SUBSTR takes everything after it, extracting the email domain. Positions start at 1, not 0. A negative start counts from the end, which neatly pulls the last part of a code. The first result shows why order matters: we extracted from the raw email, so the domain kept its capital and trailing space. Clean first, then extract. PostgreSQL uses position() or strpos(), MySQL has LOCATE, and SQL Server uses CHARINDEX and SUBSTRING.

REPLACE

SELECT replace(replace(replace(replace(replace(phone,' ',''),'-',''),'(',''),')',''),'+','')
FROM customers;
-- '919876543210', '02223456789', '9876543210'

Nested REPLACE calls stripped spaces, hyphens, brackets and plus signs, leaving digits only. It works, but it is clumsy, and the results still differ: one number has a country code, one has an area code, and one has neither. Text functions can tidy a format, but they cannot work out what a number means. For that, normalise numbers in the application with a proper library, or in databases with regular expression functions such as PostgreSQL’s regexp_replace; see our Python regex guide for the patterns.

Why cleaning matters for GROUP BY

SELECT city, COUNT(*) FROM customers GROUP BY city;
-- ('MUMBAI', 1), ('Pune ', 1), ('pune', 1)

SELECT lower(trim(city)) AS c, COUNT(*) FROM customers GROUP BY c;
-- ('mumbai', 1), ('pune', 2)

The raw query reported three cities with one customer each. Cleaned, Pune correctly has two. The same problem affects joins, DISTINCT counts and duplicate detection, which is why dirty text produces silently wrong reports. Our GROUP BY guide shows more aggregation patterns.

Padding and formatting

SELECT printf('INV-%05d', 42);   -- 'INV-00042'

Zero-padding produces fixed-width codes that sort correctly as text. SQLite uses printf (also named format); PostgreSQL and MySQL offer LPAD and RPAD, as in LPAD('42', 5, '0'); SQL Server uses FORMAT or RIGHT('00000' + value, 5). Formatting for display is often better left to the application, but padded identifiers are worth generating consistently in one place.

Unicode and length

SELECT length('नमस्ते'), length(CAST('नमस्ते' AS BLOB));
-- 6, 18

The Hindi greeting is 6 characters but 18 bytes in UTF-8. Character counts matter for validation, such as “name at most 50 characters”, while byte counts matter for storage limits and some column types. MySQL’s LENGTH returns bytes and CHAR_LENGTH returns characters; SQL Server’s LEN also ignores trailing spaces. Knowing which one you are using prevents odd truncation bugs with non-English text.

Dialect cheat sheet

The SQLite column was tested; the others are from each database’s documentation and were not run for this article.

Performance

Wrapping a column in a function, as in WHERE lower(email) = ?, usually prevents a normal index from being used, so the database scans every row. Options include storing a cleaned copy of the value, adding an index on the expression where supported (PostgreSQL, SQLite and MySQL 8 support expression indexes), or using a case-insensitive collation. The same principle applies to date functions, as we showed in SQL date functions.

Clean on the way in

String functions in queries are a repair tool. The lasting fix is to clean data when it is saved: trim and normalise case in the application or a trigger, validate formats with CHECK constraints, and store phone numbers in one canonical format. Then reports stay simple, indexes work, and duplicates are caught at the door.

A cleanup recipe

When you inherit a table of messy text, a predictable order of operations avoids most mistakes:

1. Measure the mess: count distinct raw values and compare them with lower(trim(...)) versions to see how many duplicates hide in formatting.
2. Trim first, then normalise case, because spaces and capitals affect every later step.
3. Extract or replace parts, such as domains or digits, only from the cleaned value.
4. Handle NULLs explicitly with COALESCE or CONCAT_WS wherever values are combined.
5. Preview the result beside the original in a SELECT, and review a sample by eye.
6. Update inside a transaction, check row counts, then commit, as covered in SQL transactions.
7. Stop the mess returning with validation, constraints or a trigger on insert.

Following the same steps every time makes cleanups reviewable and repeatable, instead of a series of one-off fixes.

Final thoughts

A handful of functions, TRIM, LOWER, SUBSTR, INSTR, REPLACE and CONCAT, handle most text cleanup in SQL. Watch for NULL in concatenation, hidden spaces, ASCII-only case conversion and character-versus-byte lengths. Preview every cleanup in a SELECT before an UPDATE, and move the cleaning upstream so tomorrow’s data arrives tidy.

Frequently asked questions

Keep learning