MySQL String Functions

MySQL includes a rich set of built-in functions for working with text. These functions can concatenate strings, change case, extract parts of a string, find positions, and clean up whitespace — all directly inside a SELECT statement or WHERE clause.

---

CONCAT and CONCAT_WS

CONCAT joins two or more strings together. CONCAT_WS (Concatenate With Separator) joins them with a separator between each value:

SELECT CONCAT('Rahim', ' ', 'Uddin');        -- 'Rahim Uddin'
SELECT CONCAT_WS(', ', 'Dhaka', 'Bangladesh'); -- 'Dhaka, Bangladesh'

---

LENGTH and CHAR_LENGTH

LENGTH returns the byte length; CHAR_LENGTH returns the number of characters. They differ for multi-byte character sets (e.g., UTF-8 Bengali text):

SELECT LENGTH('Hello');       -- 5
SELECT CHAR_LENGTH('Hello');  -- 5

-- For a multi-byte character, CHAR_LENGTH returns characters, LENGTH returns bytes
SELECT CHAR_LENGTH('ঢাকা');   -- 4  (4 characters)
SELECT LENGTH('ঢাকা');        -- 12 (3 bytes per character in UTF-8)

---

UPPER and LOWER

Convert text to all uppercase or all lowercase:

SELECT UPPER('hello world');  -- 'HELLO WORLD'
SELECT LOWER('HELLO WORLD');  -- 'hello world'

---

TRIM, LTRIM, RTRIM

Remove whitespace (or a specific character) from the start, end, or both sides of a string:

SELECT TRIM('  hello  ');       -- 'hello'
SELECT LTRIM('  hello  ');      -- 'hello  '
SELECT RTRIM('  hello  ');      -- '  hello'
SELECT TRIM('x' FROM 'xxxhelloxxx'); -- 'hello'

---

SUBSTRING

Extract a part of a string. The position is 1-based (first character is position 1):

SELECT SUBSTRING('RedoHub', 1, 4);   -- 'Redo'
SELECT SUBSTRING('RedoHub', 5);      -- 'Hub'  (from position 5 to end)
SELECT SUBSTR('RedoHub', -3);        -- 'Hub'  (3 characters from the end)

---

REPLACE

Replace all occurrences of a substring within a string:

SELECT REPLACE('Hello World', 'World', 'MySQL');  -- 'Hello MySQL'

This is case-sensitive — 'world' would not be replaced in the example above.

---

INSTR and LOCATE

Find the position of a substring within a string. Returns 0 if not found:

SELECT INSTR('Hello World', 'World');        -- 7
SELECT LOCATE('World', 'Hello World');       -- 7
SELECT LOCATE('World', 'Hello World', 8);   -- 0 (search starts at position 8)

---

LEFT and RIGHT

Extract a specified number of characters from the left or right side:

SELECT LEFT('Bangladesh', 6);   -- 'Bangla'
SELECT RIGHT('Bangladesh', 4);  -- 'desh'

---

LPAD and RPAD

Pad a string to a specified total length by adding a fill character on the left or right:

SELECT LPAD('42', 5, '0');    -- '00042'
SELECT RPAD('hello', 8, '.');  -- 'hello...'

---

Practical Example — Formatting Names

Combine string functions in a real query. Build a full display name and a short code from customer data:

SELECT
    CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS formatted_name,
    UPPER(LEFT(name, 3)) AS code,
    CHAR_LENGTH(TRIM(name)) AS name_length
FROM customers;

---

Quick Reference

Function	Description
CONCAT(s1, s2, ...)	Join strings; NULL in any arg returns NULL
CONCAT_WS(sep, s1, s2, ...)	Join with separator; skips NULLs
LENGTH(s)	Byte length
CHAR_LENGTH(s)	Character count
UPPER(s)	Uppercase
LOWER(s)	Lowercase
TRIM(s)	Remove leading/trailing whitespace
SUBSTRING(s, pos, len)	Extract substring
REPLACE(s, from, to)	Replace all occurrences
INSTR(s, sub)	Position of substring (0 if not found)
LEFT(s, n)	First n characters
RIGHT(s, n)	Last n characters
LPAD(s, len, pad)	Pad left to total length
RPAD(s, len, pad)	Pad right to total length
REVERSE(s)	Reverse the string