String Functions — MySQL

MySQL String Functions

MySQL provides a rich set of built-in string functions for manipulating, searching, and formatting text data. Whether you're cleaning user input, building dynamic labels, or transforming stored values for display, these functions are indispensable in day-to-day SQL work.

String Function Categories

Category	Functions
Length / size	LENGTH, CHAR_LENGTH, BIT_LENGTH
Case conversion	UPPER, LOWER
Joining / splitting	CONCAT, CONCAT_WS, SUBSTRING_INDEX, GROUP_CONCAT
Extraction	SUBSTRING, LEFT, RIGHT, MID
Search / position	INSTR, LOCATE, FIND_IN_SET
Trimming / padding	TRIM, LTRIM, RTRIM, LPAD, RPAD
Replacement / transformation	REPLACE, REGEXP_REPLACE, REVERSE
Formatting	FORMAT, SPACE, REPEAT
Phonetic / comparison	SOUNDEX, STRCMP

LENGTH vs CHAR_LENGTH

LENGTH() returns the byte length of a string. CHAR_LENGTH() (alias CHARACTER_LENGTH()) returns the number of characters. For single-byte character sets (latin1, ASCII) these are identical, but for multi-byte encodings like utf8mb4 they differ — and the difference is critical.

SQL

SELECT
  LENGTH('Hello')          AS byte_len,    -- 5
  CHAR_LENGTH('Hello')     AS char_len,    -- 5  (same for ASCII)
  LENGTH('こんにちは')     AS utf8_bytes,  -- 15 (3 bytes each in utf8)
  CHAR_LENGTH('こんにちは') AS utf8_chars; -- 5  (5 characters)

-- Practical: enforce a character limit on a column
SELECT * FROM tweets
WHERE CHAR_LENGTH(content) > 280;  -- correct: counts chars, not bytes

Note

Always use CHAR_LENGTH() when you care about the visible length of text — especially for columns stored as utf8mb4 where an emoji uses 4 bytes but is 1 character.

CONCAT and CONCAT_WS

CONCAT() joins two or more strings together. If any argument is NULL, the result is NULL. CONCAT_WS(separator, ...) (concat with separator) is safer for building delimited strings — it skips NULL arguments automatically.

SQL

-- Basic concatenation
SELECT CONCAT('Hello', ' ', 'World');          -- 'Hello World'

-- NULL propagation in CONCAT
SELECT CONCAT('Hello', NULL, 'World');         -- NULL (the whole result is NULL)

-- CONCAT_WS skips NULLs, only separator is required
SELECT CONCAT_WS(', ', 'Alice', NULL, 'Bob'); -- 'Alice, Bob'

-- Build a full name from nullable middle name
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM users;

-- Build a CSV row
SELECT CONCAT_WS(',', id, email, name, created_at) AS csv_row
FROM users LIMIT 10;

SUBSTRING_INDEX — Split by Delimiter

SUBSTRING_INDEX(str, delim, count) extracts a portion of a string by delimiter. Positive count returns everything to the left of the Nth delimiter; negative count returns everything to the right of the Nth delimiter from the end.

SQL

-- Extract domain from email
SELECT SUBSTRING_INDEX('alice@example.com', '@', -1); -- 'example.com'

-- Extract username from email
SELECT SUBSTRING_INDEX('alice@example.com', '@', 1);  -- 'alice'

-- Extract top-level domain
SELECT SUBSTRING_INDEX(
  SUBSTRING_INDEX('alice@example.com', '@', -1), '.', -1
);  -- 'com'

-- Parse first element from comma-separated list
SELECT SUBSTRING_INDEX(tags, ',', 1) AS first_tag FROM articles;

-- Parse last element from comma-separated list
SELECT SUBSTRING_INDEX(tags, ',', -1) AS last_tag FROM articles;

-- Extract IP subnet (first three octets)
SELECT SUBSTRING_INDEX('192.168.1.42', '.', 3);  -- '192.168.1'

TRIM Variants

TRIM() removes characters from both ends of a string (whitespace by default). You can specify LEADING, TRAILING, or BOTH, and optionally a character to remove instead of whitespace.

SQL

SELECT TRIM('  Hello World  ');            -- 'Hello World'
SELECT LTRIM('  Hello World  ');           -- 'Hello World  '
SELECT RTRIM('  Hello World  ');           -- '  Hello World'

-- Remove specific characters
SELECT TRIM(LEADING  '0' FROM '007');      -- '7'
SELECT TRIM(TRAILING '.' FROM '3.14.');    -- '3.14'
SELECT TRIM(BOTH     '*' FROM '***star***'); -- 'star'

-- Clean all leading/trailing slashes from a URL path
SELECT TRIM(BOTH '/' FROM '/blog/post/1/'); -- 'blog/post/1'

-- Bulk clean imported data
UPDATE customers
SET phone = TRIM(phone)
WHERE phone != TRIM(phone);

Tip

Run a TRIM update after bulk imports — extra whitespace is one of the most common data quality problems in imported datasets.

REPLACE — Bulk String Substitution

SQL

SELECT REPLACE('Hello World', 'World', 'MySQL');  -- 'Hello MySQL'
SELECT REPLACE('aabbcc', 'b', 'X');               -- 'aaXXcc'

-- Fix a typo in product descriptions (all occurrences in all rows)
UPDATE products
SET description = REPLACE(description, 'recieve', 'receive')
WHERE description LIKE '%recieve%';

-- Normalize phone format
UPDATE contacts
SET phone = REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '(', '')
WHERE phone IS NOT NULL;

LPAD and RPAD — Zero-Padding

LPAD(str, len, padstr) pads a string on the left to a total length. RPAD() pads on the right. Useful for generating fixed-width output or zero-padded identifiers like invoice numbers.

SQL

SELECT LPAD('42', 6, '0');           -- '000042'
SELECT RPAD('Hello', 10, '.');        -- 'Hello.....'

-- Generate invoice numbers like INV-00000042
SELECT CONCAT('INV-', LPAD(id, 8, '0')) AS invoice_number
FROM orders;

-- Zero-pad month and day for date sorting
SELECT LPAD(MONTH(NOW()), 2, '0');   -- '07' for July

GROUP_CONCAT — Aggregate Strings

GROUP_CONCAT is an aggregate function that concatenates string values from multiple rows into a single comma-separated (or custom-separated) string within a GROUP BY query.

SQL

-- List all product names per category
SELECT
  category_id,
  GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS products
FROM products
GROUP BY category_id;

-- Collect tag names per article
SELECT
  a.id,
  a.title,
  GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tags
FROM articles a
JOIN article_tags at ON at.article_id = a.id
JOIN tags t ON t.id = at.tag_id
GROUP BY a.id, a.title;

-- Group_concat has a 1024 byte default limit — increase if needed
SET SESSION group_concat_max_len = 65536;

Note

The default maximum length of a GROUP_CONCAT result is 1024 bytes. Increase it per-session with SET SESSION group_concat_max_len = N or globally in my.cnf with group_concat_max_len = 65536.

REGEXP_REPLACE (MySQL 8.0)

REGEXP_REPLACE(str, pattern, replacement) uses a regular expression for powerful string transformations — more flexible than REPLACE() because it matches patterns, not literal strings.

SQL

-- Strip all non-digit characters from a phone number
SELECT REGEXP_REPLACE('+1 (555) 867-5309', '[^0-9]', '');  -- '15558675309'

-- Collapse multiple spaces to a single space
SELECT REGEXP_REPLACE('too  many   spaces', '  +', ' ');   -- 'too many spaces'

-- Remove HTML tags (simple cases)
SELECT REGEXP_REPLACE('<b>Hello</b> <i>World</i>', '<[^>]+>', '');  -- 'Hello World'

-- Normalise whitespace in all user bios
UPDATE users
SET bio = REGEXP_REPLACE(TRIM(bio), '[ 	
]+', ' ')
WHERE bio IS NOT NULL;

FORMAT — Locale-Aware Number Display

SQL

SELECT FORMAT(1234567.891, 2);   -- '1,234,567.89'
SELECT FORMAT(1234567.891, 0);   -- '1,234,568'

-- Display prices in a report
SELECT product_name, CONCAT('$', FORMAT(price, 2)) AS formatted_price
FROM products;

Note

FORMAT() returns a string, not a number. Do not use it in arithmetic. Use ROUND() for numeric rounding.

Practical String Query Examples

Extract username and domain from an email:

SQL

SELECT
  email,
  LEFT(email, LOCATE('@', email) - 1)           AS username,
  SUBSTRING(email, LOCATE('@', email) + 1)      AS domain
FROM users;

Generate a URL slug from a product name:

SQL

-- Lowercase, replace spaces and slashes with dashes
SELECT
  product_name,
  LOWER(REGEXP_REPLACE(TRIM(product_name), '[^a-z0-9]+', '-')) AS slug
FROM products;

Truncate long descriptions with ellipsis:

SQL

SELECT
  product_name,
  CASE
    WHEN CHAR_LENGTH(description) > 100
      THEN CONCAT(LEFT(description, 97), '...')
    ELSE description
  END AS short_description
FROM products;

Phone number normalisation:

SQL

-- Strip everything non-numeric, then format as (XXX) XXX-XXXX
SELECT
  phone AS raw,
  REGEXP_REPLACE(phone, '[^0-9]', '') AS digits,
  CONCAT(
    '(', SUBSTRING(REGEXP_REPLACE(phone, '[^0-9]', ''), 1, 3), ') ',
    SUBSTRING(REGEXP_REPLACE(phone, '[^0-9]', ''), 4, 3), '-',
    SUBSTRING(REGEXP_REPLACE(phone, '[^0-9]', ''), 7)
  ) AS formatted
FROM contacts
WHERE CHAR_LENGTH(REGEXP_REPLACE(phone, '[^0-9]', '')) = 10;

Build a CSV row for export:

SQL

SELECT
  CONCAT_WS(',',
    id,
    CONCAT('"', REPLACE(name, '"', '""'), '"'),  -- escape quotes
    CONCAT('"', email, '"'),
    DATE_FORMAT(created_at, '%Y-%m-%d')
  ) AS csv_row
FROM users
ORDER BY id;

Quick Reference

Function	Purpose	Example Result
LENGTH(s)	Byte length	LENGTH("hi") = 2
CHAR_LENGTH(s)	Character count	CHAR_LENGTH("こ") = 1
CONCAT(a,b,...)	Join strings (NULL = NULL result)	CONCAT('a','b') = 'ab'
CONCAT_WS(sep,...)	Join with separator, skip NULLs	CONCAT_WS(',','a',NULL,'b') = 'a,b'
SUBSTRING(s,p,n)	Extract substring	SUBSTRING('Hello',2,3) = 'ell'
SUBSTRING_INDEX(s,d,n)	Split by delimiter	SUBSTRING_INDEX('a@b','@',-1) = 'b'
REPLACE(s,f,t)	Find and replace (literal)	REPLACE('aXb','X','Y') = 'aYb'
REGEXP_REPLACE(s,p,r)	Find and replace (regex, 8.0+)	REGEXP_REPLACE('a1b','[0-9]','') = 'ab'
TRIM(s)	Strip whitespace / chars	TRIM(' hi ') = 'hi'
LPAD(s,n,p)	Left-pad to width	LPAD('7',3,'0') = '007'
GROUP_CONCAT(...)	Aggregate strings across rows	GROUP_CONCAT(name) = 'Alice,Bob'
FORMAT(n,d)	Number with commas	FORMAT(1234,2) = '1,234.00'
SOUNDEX(s)	Phonetic code	SOUNDEX('Smith') = 'S530'