How to extract Mysql substring?

In MySQL we have many functions to manipulate strings: calculate their length, extract a fragment located to the right, to the left or in any position, eliminate final or initial spaces, and convert to hexadecimal and binary, among others. In this article, we are going to focus on the functions that allow us the extraction of substrings.

Here is a list of functions that allow us to do extractions of substrings in Mysql:

• SUBSTRING
• SUBSTR
• MID
• INSTR
• LEFT
• RIGHT

We will discover what each of these functions does by looking at its theoretical definition and also with usage examples.

Example

 We will be using a users table with the following content:

SUBSTRING

Definition:

We can use the function SUBSTRING() to extract a substring from a string. We have to indicate the starting point and the maximum length of the substring that we want to extract.

Input parameters:

Syntax:

Option 1: 

1. SUBSTRING(param_string, param_start, param_length)

Option 2: 

1. SUBSTRING(param_string FROM param_start FOR param_length)

Example:

Extract a substring from the text in a column (start at position 2, extract 5 characters):

1. SELECT SUBSTRING(user, 2, 5) AS ExtractString
3. FROM users;

SUBSTR AND MID

Definition:

SUBSTR() and MID() functions are synonyms for SUBSTRING(). They work in the same way and uses the same parameters.

Input parameters:

Syntax:

Option 1: 

1. SUBSTR(param_string, param_start, param_length)

Option 2: 

1. SUBSTR(param_string FROM param_start FOR param_length)

Example:

Extract a substring from the text in a column (start at position 2, extract 5 characters):

1. SELECT SUBSTR(user, 2, 5) AS ExtractString
3. FROM users;

INSTR

Definition:

This function gets the position of the first occurrence of a string in another (case-insensitive search).

Input parameters:

Syntax:

1. INSTR(param_string, param_substring)

Examples:

1. Search for the substring “IS” in the string “THIS IS A TEST”:

Parameters:

param_string = "THIS IS A TEST"

param_substring = “IS”

MySQL Query:

1. SELECT INSTR("THIS IS A TEST", "IS") AS MatchPosition;

Result:

MatchPosition = 3 (THIS IS A TEST)

2. Search for the substring “A” in the string “THIS IS A TEST”:

Parameters:

param_string = "THIS IS A TEST"

param_substring = “IS”

MySQL Query:

1. SELECT INSTR("THIS IS A TEST", "A") AS MatchPosition

Result:

MatchPosition = 9 (THIS IS A TEST)

3. Search for the substring “MY” in the string “THIS IS A TEST”:

Parameters:

param_string = "THIS IS A TEST"

param_substring = “MY”

MySQL Query:

1. SELECT INSTR("THIS IS A TEST", "MY") AS MatchPosition

Result:

MatchPosition = 0 because no match was found

LEFT

Definition:

Extracts a number of characters from a string starting from the left side. The number of characters to be returned is indicated by a parameter.

Parameters:

Syntax:

1. LEFT(param_string, param_number_of_chars)

Example:

Extract the initial of the name of each user

SELECT LEFT(name, 1) AS ExtractString FROM users;

RIGHT

Definition:

This function extracts a number of characters from a string starting from the right side. The number of characters to be returned is indicated by a parameter.

Parameters:

Syntax:

1. RIGHT(param_string, param_number_of_chars)

Examples:

Extract the last letter of the name of each user

SELECT RIGHT(name, 1) AS ExtractString FROM users;