MySQL String Functions Guide

MySQL provides a rich set of string functions for transforming, searching, and formatting text data. Whether you need to clean user input, build display names, or extract parts of a string, these functions handle it directly in SQL without requiring application-level code.

CONCAT and CONCAT_WS

CONCAT() joins strings together. CONCAT_WS() (With Separator) inserts a delimiter between each value and automatically skips NULLs.

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

-- With separator, skips NULLs
SELECT CONCAT_WS(', ', city, state, country) AS location
FROM addresses;

UPPER, LOWER, and LENGTH

Change the case of strings or measure their length. CHAR_LENGTH() counts characters whileLENGTH() counts bytes -- an important distinction for multi-byte character sets like UTF-8.

SELECT
  UPPER(last_name)       AS upper_name,
  LOWER(email)           AS lower_email,
  CHAR_LENGTH(first_name) AS name_length
FROM employees;

SUBSTRING and LEFT / RIGHT

Extract portions of a string by position. SUBSTRING(str, start, length) is the most flexible.LEFT() and RIGHT() are shortcuts for extracting from the beginning or end.

SELECT
  SUBSTRING(phone, 1, 3)  AS area_code,
  LEFT(product_code, 2)   AS category_prefix,
  RIGHT(serial_number, 4) AS last_four
FROM products;

REPLACE and TRIM

REPLACE() swaps all occurrences of a substring. TRIM() removes leading and trailing whitespace, or specific characters when specified.

-- Replace dashes in phone numbers
SELECT REPLACE(phone, '-', '') AS clean_phone
FROM customers;

-- Remove whitespace
SELECT TRIM(username) AS clean_username
FROM users;

-- Remove specific characters
SELECT TRIM(BOTH '.' FROM email_prefix) AS cleaned
FROM temp_data;

LOCATE and INSTR

Find the position of a substring within a string. Both return 0 if the substring is not found. This is useful for conditional logic in queries.

-- Find the position of '@' in an email
SELECT email, LOCATE('@', email) AS at_position
FROM users;

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

LPAD, RPAD, and REVERSE

Pad strings to a fixed width with LPAD() and RPAD(). REVERSE() flips a string. Padding is especially useful for generating formatted codes or report output.

-- Zero-pad an invoice number to 8 digits
SELECT LPAD(invoice_id, 8, '0') AS formatted_id
FROM invoices;

-- Result: '00000042'