SQL Tutorial › Functions & Grouping › String Functions

SQL String Functions

Q: How do I concatenate strings in MySQL?

Use CONCAT(a, b, ...), or CONCAT_WS(separator, a, b, ...) to add a separator and skip NULLs. MySQL does not use + or || for text by default.

Q: How do I extract part of a string in SQL?

Use SUBSTRING(text, start, length), LEFT(text, n), RIGHT(text, n), or SUBSTRING_INDEX(text, delimiter, count) to split on a character.

Q: What is the difference between LENGTH and CHAR_LENGTH?

LENGTH returns the number of bytes; CHAR_LENGTH returns the number of characters. They differ for multi-byte characters such as é or emoji.

Q: How do I remove spaces from a string in SQL?

TRIM() removes leading and trailing spaces, LTRIM() and RTRIM() one side only, and REPLACE(text, ' ', '') removes every space.

IntermediateLesson 26 of 6912 min read8 queries run on MySQL 8.4

Real data needs cleaning and formatting: full names from first and last names, domains from email addresses, codes padded with zeros, stray spaces trimmed. String functions do this inside the query, so the database returns data ready to use.

In this lesson

Join text with CONCAT and CONCAT_WS
Measure and change case with LENGTH, CHAR_LENGTH, UPPER, LOWER
Cut text with SUBSTRING, LEFT, RIGHT, SUBSTRING_INDEX
Clean text with TRIM, REPLACE, LPAD

Quick reference

Function	Example	Result
`CONCAT(a, b, ...)`	`CONCAT('SQA', 'Tools')`	SQATools
`CONCAT_WS(sep, a, b)`	`CONCAT_WS('-', 'IN', 'MH')`	IN-MH
`LENGTH(s)`	`LENGTH('café')`	5 (bytes)
`CHAR_LENGTH(s)`	`CHAR_LENGTH('café')`	4 (characters)
`UPPER(s)` / `LOWER(s)`	`UPPER('pune')`	PUNE
`SUBSTRING(s, pos, len)`	`SUBSTRING('Database', 1, 4)`	Data
`LEFT(s, n)` / `RIGHT(s, n)`	`RIGHT('2026-01', 2)`	01
`INSTR(s, sub)`	`INSTR('a@b.com', '@')`	2
`SUBSTRING_INDEX(s, d, n)`	`SUBSTRING_INDEX('a@b.com', '@', -1)`	b.com
`REPLACE(s, from, to)`	`REPLACE('2026/01/31', '/', '-')`	2026-01-31
`TRIM(s)`	`TRIM(' hi ')`	hi
`LPAD(s, n, pad)`	`LPAD('7', 3, '0')`	007
`REVERSE(s)`	`REVERSE('abc')`	cba

Sample data

customers · 7 rows

customer_id	customer_name	email	city	joined_on
1	Ananya Sharma	ananya@example.com	Mumbai	2024-01-15
2	Rohit Kumar	rohit.k@example.com	Delhi	2024-02-03
3	Fatima Khan	fatima@example.com	Hyderabad	2024-03-21
4	David Fernandes	NULL	Goa	2024-05-09
5	Ishita Bose	ishita@example.com	Kolkata	2024-07-30
6	Manoj Pillai	manoj.p@example.com	Chennai	2025-01-11
7	Sara Thomas	sara.t@example.com	Bengaluru	2025-03-02

CONCAT and CONCAT_WS

SQL

SELECT emp_id,       CONCAT(first_name, ' ', last_name)           AS full_name,       CONCAT_WS(' | ', job_title, city)            AS role_and_cityFROM employeesWHERE dept_id = 2;

ResultMySQL 8.4

emp_id	full_name	role_and_city
105	Vikram Singh	Sales Manager \| Mumbai
106	Neha Gupta	Sales Executive \| Mumbai
107	Arjun Das	Sales Executive \| Kolkata

3 rows in set

How this query works, step by step

CONCAT glues its arguments together in order; the ' ' adds the space.
CONCAT_WS (“with separator”) puts ' | ' between each pair of values.

CONCAT returns NULL if any argument is NULL, while CONCAT_WS skips NULLs:

MySQL

SELECT customer_name,       CONCAT(customer_name, ' <', email, '>')     AS with_concat,       CONCAT_WS(' ', customer_name, email)        AS with_concat_wsFROM customersWHERE customer_id IN (3, 4);

ResultMySQL 8.4

customer_name	with_concat	with_concat_ws
Fatima Khan	Fatima Khan <fatima@example.com>	Fatima Khan fatima@example.com
David Fernandes	NULL	David Fernandes

2 rows in set

UPPER, LOWER and LENGTH

MySQL

SELECT customer_name,       UPPER(customer_name)       AS upper_name,       LOWER(city)                AS lower_city,       CHAR_LENGTH(customer_name) AS name_lengthFROM customersLIMIT 3;

ResultMySQL 8.4

customer_name	upper_name	lower_city	name_length
Ananya Sharma	ANANYA SHARMA	mumbai	13
Rohit Kumar	ROHIT KUMAR	delhi	11
Fatima Khan	FATIMA KHAN	hyderabad	11

3 rows in set

Note

LENGTH counts bytes, CHAR_LENGTH counts characters. They differ for accented letters, Hindi or emoji in utf8mb4, so use CHAR_LENGTH for “how many letters”.

SUBSTRING, LEFT and RIGHT

Build a short employee code: the first three letters of the last name in capitals plus the employee id:

MySQL

SELECT first_name, last_name,       CONCAT(UPPER(LEFT(last_name, 3)), emp_id) AS emp_code,       SUBSTRING(first_name, 2, 3)              AS letters_2_to_4FROM employeesWHERE dept_id = 1;

ResultMySQL 8.4

first_name	last_name	emp_code	letters_2_to_4
Asha	Rao	RAO101	sha
Rahul	Mehta	MEH102	ahu
Priya	Nair	NAI103	riy
Karan	Shah	SHA104	ara
Amit	Verma	VER111	mit

5 rows in set

How this query works, step by step

LEFT(last_name, 3) takes the first three characters: Rao, Meh, Nai…
UPPER capitalises them and CONCAT appends the id: RAO101.
SUBSTRING(first_name, 2, 3) starts at position 2 (SQL counts from 1) and takes 3 characters.

Split an email: INSTR and SUBSTRING_INDEX

MySQL

SELECT email,       INSTR(email, '@')                 AS at_position,       SUBSTRING_INDEX(email, '@', 1)    AS username,       SUBSTRING_INDEX(email, '@', -1)   AS domainFROM customersWHERE email IS NOT NULLLIMIT 4;

ResultMySQL 8.4

email	at_position	username	domain
ananya@example.com	7	ananya	example.com
rohit.k@example.com	8	rohit.k	example.com
fatima@example.com	7	fatima	example.com
ishita@example.com	7	ishita	example.com

4 rows in set

How this query works, step by step

INSTR(email, '@') returns the position of @.
SUBSTRING_INDEX(email, '@', 1) returns everything before the first @.
With -1 it counts from the right and returns everything after the last @.

TRIM and REPLACE: clean messy input

MySQL

SELECT CONCAT('[', TRIM('   Pune  '), ']')            AS trimmed,       CONCAT('[', LTRIM('   Pune  '), ']')           AS left_trimmed,       TRIM(LEADING '0' FROM '000742')                AS no_zeros,       REPLACE('98450-12345', '-', '')                AS digits_only,       REPLACE(REPLACE(' +91 98450 ', '+91', ''), ' ', '') AS phone;

ResultMySQL 8.4

trimmed	left_trimmed	no_zeros	digits_only	phone
[Pune]	[Pune ]	742	9845012345	98450

1 row in set

How this query works, step by step

TRIM removes spaces from both ends; the brackets show where the text starts and ends.
LTRIM removes only the leading spaces.
TRIM(LEADING '0' FROM ...) removes a chosen character from the start.
REPLACE swaps every occurrence of one text for another; nesting it applies several replacements.

LPAD and RPAD

MySQL

SELECT order_id,       CONCAT('INV-', LPAD(order_id - 1000, 5, '0')) AS invoice_noFROM ordersLIMIT 3;

ResultMySQL 8.4

order_id	invoice_no
1001	INV-00001
1003	INV-00003
1008	INV-00008

3 rows in set

How this query works, step by step

order_id - 1000 turns 1001 into 1.
LPAD(..., 5, '0') pads it on the left with zeros to 5 characters: 00001.
CONCAT adds the prefix: INV-00001.

String functions in WHERE

Customers whose email is on a domain other than example.com:

MySQL

SELECT customer_name, emailFROM customersWHERE SUBSTRING_INDEX(email, '@', -1) <> 'example.com';

ResultMySQL 8.4

Empty set

Watch out

Wrapping a column in a function inside WHERE (for example WHERE UPPER(city) = 'PUNE') usually prevents MySQL from using an index on that column. Prefer conditions on the raw column, such as LIKE 'Pu%', on large tables.

Exercise: Initials

Show each employee’s initials, for example A.R. for Asha Rao.

Expected resultMySQL 8.4

first_name	last_name	initials
Meera	Kapoor	M.K.
Asha	Rao	A.R.
Rahul	Mehta	R.M.
Priya	Nair	P.N.
Karan	Shah	K.S.
Vikram	Singh	V.S.
Neha	Gupta	N.G.
Arjun	Das	A.D.
Kavya	Iyer	K.I.
Rohan	Joshi	R.J.
Sneha	Kulkarni	S.K.
Amit	Verma	A.V.

12 rows in set

Hint 1

LEFT(first_name, 1) and LEFT(last_name, 1), joined with dots by CONCAT.

MySQL

SELECT first_name, last_name,       CONCAT(LEFT(first_name, 1), '.', LEFT(last_name, 1), '.') AS initialsFROM employees;

ResultMySQL 8.4

first_name	last_name	initials
Meera	Kapoor	M.K.
Asha	Rao	A.R.
Rahul	Mehta	R.M.
Priya	Nair	P.N.
Karan	Shah	K.S.
Vikram	Singh	V.S.
Neha	Gupta	N.G.
Arjun	Das	A.D.
Kavya	Iyer	K.I.
Rohan	Joshi	R.J.
Sneha	Kulkarni	S.K.
Amit	Verma	A.V.

12 rows in set

Exercise: Email domains

Count customers per email domain, ignoring customers without an email.

Expected resultMySQL 8.4

domain	customers
example.com	6

1 row in set

Hint 1

Group by SUBSTRING_INDEX(email, '@', -1).

MySQL

SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS customersFROM customersWHERE email IS NOT NULLGROUP BY domain;

ResultMySQL 8.4

domain	customers
example.com	6

1 row in set

Quick quiz

1. What does CONCAT('a', NULL, 'b') return in MySQL?

2. At which position does SQL start counting characters?

SUBSTRING('Data', 1, 1) is D.

3. Which function returns the part of an email after the @?

4. LPAD('42', 5, '0') returns…

String functions FAQ

How do I concatenate strings in MySQL?

Use CONCAT(a, b, ...), or CONCAT_WS(separator, a, b, ...) to add a separator and skip NULLs. MySQL does not use + or || for text by default.

How do I extract part of a string in SQL?

Use SUBSTRING(text, start, length), LEFT(text, n), RIGHT(text, n), or SUBSTRING_INDEX(text, delimiter, count) to split on a character.

What is the difference between LENGTH and CHAR_LENGTH?

LENGTH returns the number of bytes; CHAR_LENGTH returns the number of characters. They differ for multi-byte characters such as é or emoji.

How do I remove spaces from a string in SQL?

TRIM() removes leading and trailing spaces, LTRIM() and RTRIM() one side only, and REPLACE(text, ' ', '') removes every space.

SQL String Functions

Quick reference

Sample data

CONCAT and CONCAT_WS

UPPER, LOWER and LENGTH

SUBSTRING, LEFT and RIGHT

Split an email: INSTR and SUBSTRING_INDEX

TRIM and REPLACE: clean messy input

LPAD and RPAD

String functions in WHERE

Exercise: Initials

Exercise: Email domains

Quick quiz

String functions FAQ

Leave a Comment Cancel reply