How to Extract a Substring From a String in MySQL

Extract a Substring From a String in MySQL

Method 1.
To exhibit the initial seven characters of each email, access the email column situated in the users table.

id	name	email	date
1	Oliver	oliver17@gmail.com	2023-05-05
2	Isla	isla-li@gmail.com	2023-05-05
3	Hudson	hudsonmel@yahoo.com	2023-05-05
4	George	george@rediffmail.com	2023-05-05

 

SELECT email, SUBSTRING(email, 1, 7) AS sub_email FROM users;

Output:

email	sub_email
oliver17@gmail.com	oliver1
isla-li@gmail.com	isla-li
hudsonmel@yahoo.com	hudsonm
george@rediffmail.com	george@

 

Another syntax with same result.

SELECT email, SUBSTRING(email FROM 1 FOR 7) AS sub_email FROM users;

Output:

email	sub_email
oliver17@gmail.com	oliver1
isla-li@gmail.com	isla-li
hudsonmel@yahoo.com	hudsonm
george@rediffmail.com	george@

 

Use the SUBSTRING() function, which requires three arguments. The initial argument denotes either the string or the column name being referenced. The second argument specifies the starting index of the character where the substring should begin. The third argument represents the length of the substring desired.

 

Method 2.

The segment of characters encompassing indices 2 through 6 (inclusive) is what you wish to exhibit.

SELECT email, SUBSTRING(email, 2, 5) AS sub_email FROM users;

Output:

email	sub_email
oliver17@gmail.com	liver
isla-li@gmail.com	sla-l
hudsonmel@yahoo.com	udson
george@rediffmail.com	eorge

 

Another syntax

SELECT email, SUBSTRING(email FROM POSITION('@' IN email)) AS sub_email FROM users;

Output:

email	sub_email
oliver17@gmail.com	@gmail.com
isla-li@gmail.com	@gmail.com
hudsonmel@yahoo.com	@yahoo.com
george@rediffmail.com	@rediffmail.com