SQL String Functions: CONCAT, SUBSTRING, TRIM and More

A string is a sequence of characters: letters, digits, spaces and symbols. A string function is a built-in function that takes text and returns a changed version of it, or a fact about it such as its length. String functions run once per row, so the result has as many rows as the input.

The classic reason to use them is messy data. In the five sample customers below, the same person was typed three different ways. To a person they are obviously the same customer; to an exact text comparison they are three different values. TRIM removes the stray spaces and LOWER makes the case consistent:

WITH customers(id, name, city, email, phone) AS (
  VALUES (1, 'priya nair',    'Delhi',  ' Priya.Nair@EXAMPLE.com ', '98234-11567'),
         (2, 'PRIYA NAIR',    'DELHI',  'priya.nair@example.com',   '98234 11567'),
         (3, '  Priya Nair ', 'delhi',  'Priya.Nair@Example.com',   ' 98234-11567 '),
         (4, 'Neha Kapoor',   'Mumbai', 'neha.kapoor@example.org',  '99123-44521'),
         (5, 'vikram singh',  'Pune',   'Vikram.Singh@example.net', '97845-22110')
)
SELECT TRIM(LOWER(name)) AS clean_name,
       COUNT(*) AS rows_in_table
FROM customers
GROUP BY TRIM(LOWER(name))
ORDER BY clean_name;

▶ Run this query in the SQL Playground

Five rows become 3 distinct customers, and the 3 spellings of Priya Nair now group together. The sample customers are written into the query with WITH ... VALUES, so you can run it in the Playground and change the rows yourself.

The names below are the common ones. The Playground runs SQLite, which spells a few of them differently; the last column shows what to use there.

CONCAT() joins two or more values end to end. It never adds a separator for you: a space, comma or dash has to be passed as its own argument. The most common use is building a full name:

SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       title
FROM employees
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

Each row combines three pieces: the first name, a literal space and the last name. Always give the result an alias such as full_name; otherwise the column is named after the whole expression.

CONCAT with more than two columns

CONCAT takes any number of arguments, so labels for reports and dropdowns can be built in one expression:

SELECT CONCAT(name, ' (', category, ') - $', price) AS product_label
FROM products
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

The || operator

Standard SQL joins strings with ||, which works in SQLite, PostgreSQL and Oracle. SQL Server uses + instead, and MySQL treats || as logical OR unless a setting is changed. The two forms give the same result until a value is NULL:

SELECT CONCAT('A', NULL, 'B') AS concat_result,
       'A' || NULL || 'B'      AS pipe_result;

▶ Run this query in the SQL Playground

In SQLite, PostgreSQL and SQL Server, CONCAT() skips NULL arguments and returns AB, while || (and SQL Server's +) returns NULL as soon as one part is NULL. MySQL is the exception: its CONCAT() returns NULL if any argument is NULL. Wrapping a nullable column in COALESCE, for example COALESCE(middle_name, ''), gives the same answer in every database.

UPPER() converts every letter to uppercase and LOWER() to lowercase. Digits, spaces and symbols pass through unchanged, so they are safe to apply to codes and email addresses.

SELECT first_name,
       UPPER(last_name) AS last_name_upper,
       LOWER(department) AS department_lower
FROM employees
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

Case-insensitive comparisons

Whether = cares about case depends on the database. In SQLite and PostgreSQL it does; MySQL and SQL Server usually do not, because their default collations are case-insensitive. The Playground stores cities as "New York", so compare an exact match with a lowercased one:

SELECT SUM(city = 'new york')        AS exact_match,
       SUM(LOWER(city) = 'new york') AS lower_match
FROM users;

▶ Run this query in the SQL Playground

The exact comparison finds 0 users and the lowercased one finds 7. Lowering both sides is the portable way to match text whatever its case. (In SQLite, SUM() of a comparison counts the rows where it is true.)

Which one to use

Neither case is more correct. What matters is using the same convention everywhere a value is compared or grouped, so two rows that mean the same thing always look the same to the database.

LENGTH() returns the number of characters in a string, spaces included. SQL Server calls it LEN(), and LEN() ignores trailing spaces. Here are the longest product names in the Playground:

SELECT DISTINCT name,
       LENGTH(name) AS name_length
FROM products
ORDER BY name_length DESC, name
LIMIT 5;

▶ Run this query in the SQL Playground

LENGTH is also a quick data-quality check. Every SKU in the Playground should have the same format, so grouping by its length should give one group:

SELECT LENGTH(sku) AS sku_length,
       COUNT(*) AS products
FROM products
GROUP BY LENGTH(sku);

▶ Run this query in the SQL Playground

All 120 SKUs are 10 characters long. A second row here would point straight at malformed codes.

Spaces count

Untrimmed spaces change the length, which is a common reason why a value "looks right" but does not match. Compare the raw and trimmed lengths of the sample customers:

WITH customers(id, name, city, email, phone) AS (
  VALUES (1, 'priya nair',    'Delhi',  ' Priya.Nair@EXAMPLE.com ', '98234-11567'),
         (2, 'PRIYA NAIR',    'DELHI',  'priya.nair@example.com',   '98234 11567'),
         (3, '  Priya Nair ', 'delhi',  'Priya.Nair@Example.com',   ' 98234-11567 '),
         (4, 'Neha Kapoor',   'Mumbai', 'neha.kapoor@example.org',  '99123-44521'),
         (5, 'vikram singh',  'Pune',   'Vikram.Singh@example.net', '97845-22110')
)
SELECT id,
       '[' || name || ']' AS name_with_edges,
       LENGTH(name)       AS raw_length,
       LENGTH(TRIM(name)) AS trimmed_length
FROM customers;

▶ Run this query in the SQL Playground

The square brackets show where each value really starts and ends. Row 3 is 13 characters long but only 10 after trimming.

TRIM() removes spaces from both ends of a string and leaves the spaces in the middle alone. LTRIM() removes them only from the left (the start) and RTRIM() only from the right (the end):

SELECT '[' || TRIM('  Priya Nair  ')  || ']' AS trim_result,
       '[' || LTRIM('  Priya Nair  ') || ']' AS ltrim_result,
       '[' || RTRIM('  Priya Nair  ') || ']' AS rtrim_result;

▶ Run this query in the SQL Playground

TRIM can remove other characters too. In SQLite the second argument lists the characters to strip, for example TRIM('--SQL--', '-') returns SQL. The standard form, used by PostgreSQL and MySQL, is TRIM(BOTH '-' FROM '--SQL--').

SUBSTRING(string, start, length) returns length characters starting at position start. Positions start at 1, not 0. If you leave out the length, you get everything to the end of the string. SQLite accepts both SUBSTR and SUBSTRING.

Every Playground SKU looks like SKU-000001: a three-letter prefix, a dash and six digits. SUBSTR splits it into its parts:

SELECT sku,
       SUBSTR(sku, 1, 3) AS prefix,
       SUBSTR(sku, 5)    AS digits,
       CAST(SUBSTR(sku, 5) AS INTEGER) AS sku_number
FROM products
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

SUBSTR(sku, 5) starts at the fifth character, right after the dash, and runs to the end. Casting the digits to an integer drops the leading zeros.

Start position from a search

When the piece you want does not sit at a fixed position, find it first. INSTR(email, '@') returns the position of the @, so everything before it is the username and everything after it is the domain:

SELECT email,
       SUBSTR(email, 1, INSTR(email, '@') - 1) AS username,
       SUBSTR(email, INSTR(email, '@') + 1)    AS domain
FROM users
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

Asking for more characters than are left is not an error: SUBSTR('SQL', 2, 100) simply returns QL.

LEFT(string, n) returns the first n characters and RIGHT(string, n) the last n. MySQL, PostgreSQL and SQL Server have both. SQLite has neither, but a negative start position in SUBSTR counts from the end, so SUBSTR(x, 1, n) is LEFT and SUBSTR(x, -n) is RIGHT.

A typical use is masking: show only the last four digits of a phone number. Initials are the first character of each name:

SELECT first_name, last_name,
       SUBSTR(first_name, 1, 1) || SUBSTR(last_name, 1, 1) AS initials,
       phone,
       '***-***-' || SUBSTR(phone, -4) AS masked_phone
FROM users
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

-- MySQL, PostgreSQL, SQL Server
SELECT LEFT(first_name, 1) AS first_initial,
       RIGHT(phone, 4)     AS last_four
FROM users;

REPLACE(string, search, replacement) replaces every occurrence of search. Passing an empty string as the replacement deletes it. If the search value is not found, the string comes back unchanged. Stripping the dashes and the plus sign leaves only the digits of each phone number:

SELECT phone,
       REPLACE(REPLACE(phone, '-', ''), '+', '') AS digits_only
FROM users
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

REPLACE handles one search value per call, so replacing several things means nesting it. Here two nested calls plus LOWER turn the product categories into URL slugs: the inner REPLACE spells out "&", the outer one turns spaces into dashes.

SELECT DISTINCT category,
       LOWER(REPLACE(REPLACE(category, ' & ', ' and '), ' ', '-')) AS url_slug
FROM products
ORDER BY category;

▶ Run this query in the SQL Playground

Every database can tell you where a substring first appears, but each names the function differently. All of them return 0 when the text is not found, which makes them handy for validation:

SELECT INSTR(email, '@') AS at_position,
       COUNT(*) AS users
FROM users
GROUP BY INSTR(email, '@')
ORDER BY at_position
LIMIT 6;

▶ Run this query in the SQL Playground

No group has position 0, so every email in the Playground contains an @. A filter such as WHERE INSTR(email, '@') = 0 is the quick way to find addresses that do not.

REVERSE()

REVERSE('SQL') returns LQS. MySQL, PostgreSQL and SQL Server have it; SQLite does not. It is occasionally used to find the last occurrence of a character, by searching the reversed string.

-- MySQL, PostgreSQL, SQL Server
SELECT name, REVERSE(name) AS reversed_name
FROM products;

String functions can be used anywhere an expression is allowed. Grouping by an extracted piece of text is one of the most useful patterns. This counts users per email provider:

SELECT SUBSTR(email, INSTR(email, '@') + 1) AS email_domain,
       COUNT(*) AS users
FROM users
GROUP BY email_domain
ORDER BY users DESC, email_domain;

▶ Run this query in the SQL Playground

Inside CASE WHEN, a string function can sort rows into groups. Here product names are labelled by length:

SELECT CASE WHEN LENGTH(name) <= 15 THEN 'Short'
            WHEN LENGTH(name) <= 25 THEN 'Medium'
            ELSE 'Long'
       END AS name_length_group,
       COUNT(*) AS products
FROM products
GROUP BY name_length_group
ORDER BY products DESC;

▶ Run this query in the SQL Playground

And in WHERE, a function filters on part of a value. This finds employees whose title contains "Manager", using LIKE with wildcards:

SELECT title,
       COUNT(*) AS employees
FROM employees
WHERE title LIKE '%Manager%'
GROUP BY title
ORDER BY employees DESC, title;

▶ Run this query in the SQL Playground

Real cleaning chains several functions. This query takes the five sample customers and, in one pass, trims and capitalises the name, lowercases the city and email, and reduces the phone number to digits:

WITH customers(id, name, city, email, phone) AS (
  VALUES (1, 'priya nair',    'Delhi',  ' Priya.Nair@EXAMPLE.com ', '98234-11567'),
         (2, 'PRIYA NAIR',    'DELHI',  'priya.nair@example.com',   '98234 11567'),
         (3, '  Priya Nair ', 'delhi',  'Priya.Nair@Example.com',   ' 98234-11567 '),
         (4, 'Neha Kapoor',   'Mumbai', 'neha.kapoor@example.org',  '99123-44521'),
         (5, 'vikram singh',  'Pune',   'Vikram.Singh@example.net', '97845-22110')
)
SELECT id,
       UPPER(SUBSTR(TRIM(name), 1, 1)) || LOWER(SUBSTR(TRIM(name), 2)) AS clean_name,
       LOWER(TRIM(city))  AS clean_city,
       LOWER(TRIM(email)) AS clean_email,
       REPLACE(REPLACE(TRIM(phone), '-', ''), ' ', '') AS clean_phone
FROM customers;

▶ Run this query in the SQL Playground

Rows 1 to 3 now have identical values in every column, so they can be found with a GROUP BY and merged. Functions apply from the inside out: TRIM(name) runs first, then SUBSTR, then UPPER or LOWER.

Find the duplicates

WITH customers(id, name, city, email, phone) AS (
  VALUES (1, 'priya nair',    'Delhi',  ' Priya.Nair@EXAMPLE.com ', '98234-11567'),
         (2, 'PRIYA NAIR',    'DELHI',  'priya.nair@example.com',   '98234 11567'),
         (3, '  Priya Nair ', 'delhi',  'Priya.Nair@Example.com',   ' 98234-11567 '),
         (4, 'Neha Kapoor',   'Mumbai', 'neha.kapoor@example.org',  '99123-44521'),
         (5, 'vikram singh',  'Pune',   'Vikram.Singh@example.net', '97845-22110')
)
SELECT LOWER(TRIM(email)) AS clean_email,
       COUNT(*) AS rows_found
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

▶ Run this query in the SQL Playground

Without TRIM and LOWER, no two of those emails would be equal and the query would find no duplicates at all.

The ideas transfer between databases; only some names change. Check the function name for your target database before you move a query from one to another.

Wrapping a column in a function inside WHERE, as in WHERE UPPER(city) = 'DELHI', usually stops the database from using an ordinary index on city. The index stores the original values, not the uppercased ones, so every row has to be read and converted. On a small table that does not matter; on millions of rows it can.

There are two common fixes. One is an expression index on the function itself: PostgreSQL, Oracle and SQLite support CREATE INDEX ... ON users (LOWER(email)), MySQL from 8.0.13, and SQL Server through an indexed computed column. The other is to clean values once, when they are written, so queries can compare them directly. Use EXPLAIN to check whether a query uses an index before changing anything.

Six errors that give wrong results, often without an error message.

SELECT SUBSTR(sku, 0, 3) AS prefix
FROM products;

SELECT SUBSTR(sku, 1, 3) AS prefix
FROM products;

SELECT CONCAT(first_name, last_name) AS full_name
FROM employees;

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

SELECT first_name || ' ' || middle_name || ' ' || last_name
FROM people;

SELECT first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name
FROM people;

SELECT *
FROM users
WHERE city = 'new york';

SELECT *
FROM users
WHERE LOWER(city) = 'new york';

SELECT *
FROM customers
WHERE LENGTH(name) = 10;

SELECT *
FROM customers
WHERE LENGTH(TRIM(name)) = 10;

SELECT LEN(name)
FROM products;

SELECT LENGTH(name)
FROM products;

6 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Show each employee's full name as "last_name, first_name", sorted alphabetically. Show the first 10. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT last_name || ', ' || first_name AS employee
   FROM employees
   ORDER BY last_name, first_name
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   || joins the pieces; the comma and space are a literal string between them.

2. 2Easy List every department name in uppercase with the number of characters in it, longest first. Table: departments

   Show solution

   SQLite · runs in the Playground

   SELECT UPPER(name) AS department,
          LENGTH(name) AS characters
   FROM departments
   ORDER BY characters DESC, department;

   ▶ Run this query in the SQL Playground

   UPPER changes the display only; LENGTH counts the characters of the original name, including the space in "Customer Success".

3. 3Medium Count the users per email domain, but only for domains that end in ".com". Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT SUBSTR(email, INSTR(email, '@') + 1) AS email_domain,
          COUNT(*) AS users
   FROM users
   WHERE email LIKE '%.com'
   GROUP BY email_domain
   ORDER BY users DESC;

   ▶ Run this query in the SQL Playground

   INSTR finds the @, SUBSTR takes everything after it, and LIKE with a leading wildcard keeps addresses ending in .com.

4. 4Medium Show the SKU number (without the "SKU-" prefix and leading zeros) and name of the five most expensive products. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT CAST(SUBSTR(sku, 5) AS INTEGER) AS sku_number,
          name,
          price
   FROM products
   ORDER BY price DESC
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   SUBSTR(sku, 5) skips the four characters "SKU-"; CAST to INTEGER removes the leading zeros.

5. 5Medium Build a login name for each employee: the first letter of the first name plus the last name, all lowercase. Show the first 8 by id. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT first_name, last_name,
          LOWER(SUBSTR(first_name, 1, 1) || last_name) AS login
   FROM employees
   ORDER BY id
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   SUBSTR(first_name, 1, 1) is the first initial; LOWER is applied to the joined result so both parts are lowercase.

6. 6Hard Find the review comments that mention "quality" in any case, with how many reviews use each one. Table: reviews

   Show solution

   SQLite · runs in the Playground

   SELECT comment,
          COUNT(*) AS reviews
   FROM reviews
   WHERE LOWER(comment) LIKE '%quality%'
   GROUP BY comment
   ORDER BY reviews DESC, comment;

   ▶ Run this query in the SQL Playground

   Lowercasing the comment before the LIKE comparison makes the search case-insensitive in every database.