String Functions

Beginner

Advanced Queries

CONCAT, SUBSTRING, LENGTH, REPLACE, UPPER/LOWER, TRIM, REGEXP_REPLACE, and FORMAT are core string functions; MySQL is case-insensitive for string comparison by default (COLLATION-dependent).

Overview

MySQL provides a rich set of string manipulation functions for transforming, searching, and formatting text data. Key functions: CONCAT / CONCAT_WS (join strings), SUBSTRING / LEFT / RIGHT (extract), LENGTH / CHAR_LENGTH (size in bytes vs chars), UPPER / LOWER / TRIM (normalise), REPLACE / REGEXP_REPLACE (substitution), LOCATE / INSTR (search), LPAD / RPAD (padding), and FORMAT (number formatting). Collation determines case-sensitivity: most utf8mb4_unicode_ci collations are case-insensitive by default.

Core String Functions

CONCAT joins strings; CONCAT_WS uses a separator. SUBSTRING(str, pos, len) extracts; LENGTH counts bytes; CHAR_LENGTH counts characters (important for multibyte UTF-8).

SQL — CONCAT, SUBSTRING, LENGTH, TRIM

-- CONCAT and CONCAT_WS
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT CONCAT_WS(', ', city, state, country) AS address FROM locations;
-- CONCAT_WS skips NULL values; CONCAT returns NULL if any arg is NULL

-- SUBSTRING
SELECT SUBSTRING('Hello World', 1, 5);   -- 'Hello'  (1-indexed)
SELECT LEFT('Hello World', 5);            -- 'Hello'
SELECT RIGHT('Hello World', 5);           -- 'World'
SELECT MID('Hello World', 7, 5);          -- 'World'  (alias for SUBSTRING)

-- Length: bytes vs characters
SELECT LENGTH('こんにちは');       -- 15 (5 chars × 3 bytes each in utf8)
SELECT CHAR_LENGTH('こんにちは');  -- 5  (5 characters)
-- Always use CHAR_LENGTH for user-visible string length checks

-- Case and trim
SELECT UPPER('hello WORLD');   -- 'HELLO WORLD'
SELECT LOWER('HELLO world');   -- 'hello world'
SELECT TRIM('  hello  ');      -- 'hello'
SELECT LTRIM('  hello');       -- 'hello'
SELECT RTRIM('hello  ');       -- 'hello'

Search and Replace

LOCATE returns position (0 = not found). REPLACE does literal substitution. REGEXP_REPLACE (MySQL 8.0+) uses regex patterns. LIKE uses simple wildcards.

SQL — LOCATE, REPLACE, REGEXP_REPLACE

-- LOCATE / INSTR — find position
SELECT LOCATE('World', 'Hello World');  -- 7
SELECT INSTR('Hello World', 'World');   -- 7 (alias for LOCATE with args reversed)

-- REPLACE — literal substitution
SELECT REPLACE('Hello World', 'World', 'MySQL');  -- 'Hello MySQL'
UPDATE products SET sku = REPLACE(sku, '-v1', '-v2') WHERE sku LIKE '%-v1';

-- REGEXP_REPLACE (MySQL 8.0+) — regex substitution
SELECT REGEXP_REPLACE('Phone: 098-765-4321', '[0-9-]', '');
-- 'Phone: '  (strips all digits and hyphens)

-- Mask credit card number
SELECT REGEXP_REPLACE('4111-1111-1111-1111', '[0-9](?=([0-9]{4}))', 'X');
-- 'XXXX-XXXX-XXXX-1111'

-- REGEXP_SUBSTR (MySQL 8.0+) — extract matching part
SELECT REGEXP_SUBSTR('Order #ORD-2024-001 shipped', 'ORD-[0-9-]+');
-- 'ORD-2024-001'

-- Case-insensitive comparison (depends on collation)
-- utf8mb4_unicode_ci: case-insensitive by default
SELECT * FROM products WHERE name = 'widget';  -- matches 'Widget', 'WIDGET'
-- Force case-sensitive search:
SELECT * FROM products WHERE BINARY name = 'widget';

Formatting & Padding

FORMAT(n, d) formats a number with commas and d decimal places. LPAD / RPAD pads strings to a target length. These are commonly used in reporting queries.

SQL — FORMAT, LPAD, RPAD, REPEAT

-- FORMAT — number formatting with locale-aware commas
SELECT FORMAT(1234567.89, 2);    -- '1,234,567.89'
SELECT FORMAT(total, 2) AS formatted_total FROM orders;

-- LPAD / RPAD — pad to fixed width
SELECT LPAD(order_id, 8, '0');   -- '00000042' (left-pad with zeros)
SELECT RPAD(name, 20, '.');       -- 'Alice...............' (right-pad with dots)
-- Useful for fixed-width reports or generating padded IDs

-- REPEAT — repeat a string N times
SELECT REPEAT('*', 5);           -- '*****'

-- REVERSE
SELECT REVERSE('MySQL');         -- 'LQSyM'

-- ELT — index-based string lookup (poor man's CASE for small enums)
SELECT ELT(status_code, 'Draft', 'Placed', 'Shipped', 'Delivered')
FROM orders;
-- status_code=1 → 'Draft', 2 → 'Placed', etc.

-- Practical: generate a report row
SELECT
    LPAD(id, 6, '0')          AS order_ref,
    RPAD(customer_name, 30, ' ') AS customer,
    FORMAT(total, 2)           AS total
FROM orders
ORDER BY created_at DESC LIMIT 10;

Key Points to Remember

1CONCAT_WS is safer than CONCAT when some arguments may be NULL (NULLs are skipped).
2Use CHAR_LENGTH for character count in multibyte encodings; LENGTH returns byte count.
3REGEXP_REPLACE and REGEXP_SUBSTR require MySQL 8.0+.
4Most utf8mb4_unicode_ci comparisons are case-insensitive; use BINARY for case-sensitive search.
5FORMAT(n, d) produces locale-formatted numbers; LPAD/RPAD produce fixed-width strings.
6String function calls in WHERE clauses prevent index use — avoid on indexed columns.

Interview Questions

What is the difference between LENGTH() and CHAR_LENGTH() in MySQL?

EasyTCS

Why does CONCAT() return NULL if any argument is NULL, and how do you avoid this?

EasyInfosys

How would you perform a case-sensitive string comparison in MySQL?

MediumAmazon

Why should you avoid using string functions on indexed columns in WHERE clauses?

MediumFlipkart

How would you mask all but the last 4 digits of a credit card number in MySQL?

HardRazorpay

Ask Aria about String Functions

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Views

Date & Time Functions