How to Extract a Substring in MySQL

Problem:

You want to extract a substring from the text in a given column.

Example:

Our database has a table named web_address with data in the columns id and address. We want to remove the ‘www.’ at the beginning and the ‘.com’ at the end of each address.

Solution 1:

To remove the first 4 characters:

SELECT
SUBSTR(address, 5, LENGTH(address) -4) AS substring
FROM web_address;

To remove the last 4 characters:

SELECT
SUBSTR(address, 1, LENGTH(address) -4) AS substring
FROM web_address;

To remove the first 4 characters and the last 4 characters:

SELECT
SUBSTR(address, 5, LENGTH(address) -8) AS substring
FROM web_address;

Solution 2:

To remove the first 4 characters:

SELECT
SUBSTRING(address, 5, LENGTH(address)) AS substring
FROM web_address;

To remove the last 4 characters:

SELECT
SUBSTRING(address, 1, LENGTH(address) -4) AS substring
FROM web_address;

To remove the first 4 characters and the last 4 characters:

SELECT
SUBSTRING(address, 5, LENGTH(address) -8) AS substring
FROM web_address;

Solution 3:

To remove all characters before the second ‘.’ from the right:

SELECT
SUBSTRING_INDEX(address, '.', -2) AS substring
FROM web_address;

To remove all characters after the second ‘.’ from the left:

SELECT
SUBSTRING_INDEX(address, '.', 2) AS substring
FROM web_address;

To remove all characters after the second ‘.’ from the left, then remove all characters before the first ‘.’ from the right:

SELECT
SUBSTRING_INDEX(
	SUBSTRING_INDEX(address, '.', 2),
	'.',
	-1
) AS substring
FROM web_address;

The result is:

Discussion:

The first and the second solutions both remove a specified number of characters from the text with the SUBSTR() or SUBSTRING() function. SUBSTR() is a synonym for SUBSTRING(). They both require the string and the starting position as arguments. The last argument, which defines the number of characters to extract, is optional. If the last argument is omitted, the whole string (from the starting point) will be returned.

In the third solution, the SUBSTRING_INDEX() function removes the text before or after the specified characters. It requires as arguments the string, the character, and how many instances of the character should be encountered to where the text extraction starts.