String Functions

Measure, cut, clean and join text in MySQL 8.4 with CHAR_LENGTH, SUBSTRING, REPLACE, TRIM, CONCAT and CONCAT_WS.

SyntaxCHAR_LENGTH(str) | SUBSTRING(str, pos [, len]) | REPLACE(str, from_str, to_str) | TRIM([{BOTH|LEADING|TRAILING} [remstr] FROM] str) | CONCAT_WS(separator, str1, str2, ...)

String functions reshape text inside the query, so imported data can be normalised, display labels built, and parts of a column matched without a round trip to application code.

Length is two different questions

LENGTH(str) returns bytes; CHAR_LENGTH(str) returns code points. Under the utf8mb4 character set MySQL 8.4 uses by default, 'café' is 5 bytes but 4 characters. Use CHAR_LENGTH() for anything a person would count; CHARACTER_LENGTH() is the same function spelled out.

Cutting and replacing

SUBSTRING(str, pos) and SUBSTRING(str, pos, len) are 1-indexed, and a negative pos counts back from the end — SUBSTRING('Sakila', -3) is 'ila'. SUBSTR() and MID() are synonyms, and the standard-SQL spelling SUBSTRING(str FROM pos FOR len) is the same call in longhand.

REPLACE(str, from_str, to_str) replaces every occurrence and matches case-sensitively. TRIM(str) is shorthand for TRIM(BOTH ' ' FROM str); the full form TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) strips any substring you name, while LTRIM() and RTRIM() only ever remove spaces.

Joining

CONCAT() returns NULL if any argument is NULL. CONCAT_WS(separator, ...) inserts the separator between values and skips NULL arguments — though not empty strings, which still produce a doubled separator. If the separator itself is NULL, the result is NULL.

Example

Example · sql
-- Clean up a staging table of imported contacts for display
SELECT
  id,
  -- TRIM with no specifier means TRIM(BOTH ' ' FROM raw_name)
  TRIM(raw_name)                                       AS name,
  CHAR_LENGTH(TRIM(raw_name))                          AS name_chars,   -- characters
  LENGTH(TRIM(raw_name))                               AS name_bytes,   -- bytes; accents cost 2 in utf8mb4
  -- A NULL middle_name would make CONCAT() return NULL; CONCAT_WS skips it
  CONCAT_WS(' ', first_name, middle_name, last_name)   AS full_name,
  -- Strip formatting so '+1 (555) 010-9999' can be compared with stored digits
  REPLACE(REPLACE(REPLACE(REPLACE(phone, ' ', ''), '(', ''), ')', ''), '-', '')
                                                       AS phone_digits,
  SUBSTRING(email, 1, 3)                               AS email_prefix, -- positions start at 1
  SUBSTRING(email, -4)                                 AS email_tail    -- negative pos counts from the end
FROM imported_contacts
WHERE TRIM(raw_name) <> '';

When to use it

  • Normalise whitespace and casing in a staging table before loading it into production
  • Build a display name from first, middle and last name columns where the middle name is often NULL
  • Strip punctuation from phone numbers or postcodes so they can be compared consistently
  • Check whether a VARCHAR column will fit a byte-limited index by comparing LENGTH against CHAR_LENGTH

More examples

Group signups by email domain

SUBSTRING_INDEX splits on a delimiter without regular expressions, which is the usual way to pull a domain, a file extension, or the last segment of a path. Reach for it in any ad hoc report over free-text columns; note that the expression is not indexable, so keep it out of the WHERE clause on large tables.

Example · sql
-- SUBSTRING_INDEX(str, delim, count): count > 0 takes from the left,
-- count < 0 takes from the right, so -1 is everything after the last '@'
SELECT
  LOWER(SUBSTRING_INDEX(email, '@', -1)) AS domain,
  COUNT(*)                               AS signups
FROM users
WHERE email LIKE '%@%'
GROUP BY domain
ORDER BY signups DESC
LIMIT 20;

The same substring, four spellings

SUBSTR and MID are aliases of SUBSTRING, and the FROM/FOR form is the SQL-standard spelling of the comma form. You will meet all four in other people's code and in ports from PostgreSQL or Oracle; recognising them as one function saves hunting the manual for a difference that does not exist.

Example · sql
SELECT
  SUBSTRING('Quadratically', 5, 6)            AS comma_form,  -- 'ratica'
  SUBSTRING('Quadratically' FROM 5 FOR 6)     AS standard_form,
  SUBSTR('Quadratically', 5, 6)               AS substr_alias,
  MID('Quadratically', 5, 6)                  AS mid_alias;

-- Likewise for TRIM: these two are identical
SELECT TRIM('  bar   ') AS short, TRIM(BOTH ' ' FROM '  bar   ') AS long;

Strip a known prefix, not just whitespace

TRIM with LEADING or TRAILING and an explicit remstr removes a prefix or suffix only at the ends, while REPLACE removes every occurrence anywhere in the string. Use TRIM for one-off data migrations where an interior match would corrupt the value.

Example · sql
-- Legacy SKUs arrived as 'SKU-000481'; the catalogue wants '000481'
UPDATE products
SET sku = TRIM(LEADING 'SKU-' FROM sku)
WHERE sku LIKE 'SKU-%';

-- REPLACE would also hit an internal occurrence, which TRIM will not:
SELECT REPLACE('SKU-1-SKU-2', 'SKU-', '')          AS replaced,   -- '1-2'
       TRIM(LEADING 'SKU-' FROM 'SKU-1-SKU-2')     AS trimmed;    -- '1-SKU-2'

Related

sql printfsql likesql nullsql castsql group concat

Discussion

  • Be the first to comment on this lesson.