MySQL SUBSTRING() and SUBSTR() Functions – How to extract substrings in MySQL?

By /
SUBSTRING And SUBSTR Functions

In this tutorial, we will learn about the MySQL SUBSTRING() and SUBSTR() functions. Both functions do the same thing but have different names. SUBSTRING() and SUBSTR() are used to extract a substring from a given string from a given position. You may also specify the number of characters you wish to extract into the substring. Both are usually used with the SELECT Statement.

Syntax of MySQL SUBSTRING() and SUBSTR()

Both functions have similar syntaxes, the difference lying only in the function name. Both functions have two syntaxes. You may use either one of them.

For SUBSTRING()

SUBSTRING(string, start, length);Code language: SQL (Structured Query Language) (sql)

OR

SUBSTRING(string FROM start FOR length);Code language: SQL (Structured Query Language) (sql)

For SUBSTR()

SUBSTR(string, start, length);Code language: SQL (Structured Query Language) (sql)

OR

SUBSTR(string FROM start FOR length);Code language: SQL (Structured Query Language) (sql)

For both functions:

  • Parameter ‘string’ is the string from which you want to extract the substring,
  • Parameter ‘start’ is the position in ‘string’ from where you want to begin extracting the substring and,
  • Parameter ‘length’ is the number of characters that you want to extract. This is an optional parameter and if omitted, the whole string from the ‘start’ position will be returned.

Examples of MySQL SUBSTRING() and SUBSTR()

Let’s get down to some examples of the SUBSTRING() in MySQL. We will make use of aliases to make our output readable.

SELECT SUBSTR(‘Geography’, 4, 5) AS Result; 
SELECT SUBSTR(‘Geography’ FROM 4 FOR 5) AS Result; 
SELECT SUBSTR(‘North America’ FROM 7) AS Result; 
SELECT SUBSTR(‘North America’, 7) AS Result;Code language: SQL (Structured Query Language) (sql)

The first two queries essentially mean the same. Same applies for the last two queries. In the first two queries, we are extracting 5 characters from the string ‘Geography’ starting from position 4.

In the last two queries, we are extracting all characters after position 7 from the string ‘North America’. I have demonstrated both syntaxes so that you get a clear picture. Use whichever syntax you feel comfortable with.

The output we get is,

Substr Example

Let us look at the below queries.

SELECT SUBSTRING(‘New Delhi is the capital of India.’, 29, 5) AS Country; 
SELECT SUBSTRING(‘New Delhi is the capital of India.’ FROM 29 FOR 5) AS Country; 
SELECT SUBSTRING(‘JournalDev’, 8) AS Result; 
SELECT SUBSTRING(‘JournalDev’ FROM 8) AS Result;Code language: SQL (Structured Query Language) (sql)

Again, the first two queries essentially mean the same. Same applies for the last two queries. In the first two queries, we are extracting 5 characters from the string ‘New Delhi is the capital of India.’ starting from position 29. In the last two queries, we are extracting all characters after position 8 from the string ‘JournalDev’.

Use whichever syntax you feel comfortable with. The output we get is,

MySQL Substring Example

Using Negative Integer

Negative integers do get interpreted by MySQL SUBSTRING() and SUBSTR(). Negative integers count the string backward. So if you say position -1, then that is the last character in the string.

If you have a string with the value – ‘Hello’ and you specify the position parameter as -1, then at -1 you have the letter ‘o’.

Let us look at a few examples with SUBSTRING() and SUBSTR() with negative values as the position parameter.

SELECT SUBSTRING(‘Intelligence’, -1) AS Result; 
SELECT SUBSTRING(‘Intelligence’, -5, 3) AS Result;Code language: SQL (Structured Query Language) (sql)

In the second query, we count 5 characters backwards to get the letter ‘g’ and then you extract three characters from that position which includes ‘g’. The output is,

Substring Negative Example

Let us look at examples with SUBSTR().

SELECT SUBSTR(‘Intelligence’, -12) AS Result; 
SELECT SUBSTR(‘Intelligence’, -12, 5) AS Result;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Substr Negative Example

Working with Table Data – SUBSTRING() and SUBSTR()

Consider the below Employee table.

Employee Table Reverse
Employee Table

Example 1

How about extracting 3 characters, starting from position 4 from the Department column values? Let us not return the repetitive values. For that, we will use the DISTINCT keyword.

SELECT DISTINCT SUBSTR(Department, 4, 3) AS Result FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Substr Substring Table 1

Example 2

Now lets us use an alias named DepartmentCode and display that in our result set. DepartmentCode contains the first three characters of every department in uppercase. Again, we will return only the distinct values and make use of the UPPER() function.

SELECT DISTINCT Department, UPPER(SUBSTRING(Department, 1, 3)) AS DepartmentCode FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is,

Substr Substring Table 2

Example 3

How about extracting the first names of the employees from the table? But, the number of characters to be extracted for each name would be different right? So we make use of the POSITION() function. Our query is:

SELECT SUBSTR(Name, 1, POSITION(“ “ IN Name)) AS FirstName FROM Employee;Code language: SQL (Structured Query Language) (sql)

And our output is:

Substr Substring Table 3

Conclusion

SUBSTRING() and SUBSTR() are one of the most widely used string functions in MySQL. I would suggest you play around with them and practice.

References