MySQL SUBSTRING_INDEX Function

Summary: in this tutorial, you will learn how to use the MySQL SUBSTRING_INDEX() function to get a substring from a string before a specified number of occurrences of the delimiter.

MySQL SUBSTRING_INDEX() function overview

The SUBSTRING_INDEX() function returns a substring from a string before a specified number of occurrences of the delimiter.

Here is the syntax of the SUBSTRING_INDEX() function:

SUBSTRING_INDEX(str,delimiter,n)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• str is the string from which you want to extract a substring.
• delimiter is a string that acts as a delimiter. The function performs a case-sensitive match when searching for the delimiter.
• n is an integer that specifies the number of occurrences of the delimiter. The n can be negative or positive. If n is positive, the function returns every character from the left of the string up to n number of occurrences of the delimiter. If n is negative, the function returns every character from right up to n number of occurrences of the delimiter.

MySQL SUBSTRING_INDEX() function examples

Let’s take some examples of using the SUBSTRING_INDEX() function.

1) Using MySQL SUBSTRING_INDEX() function with a positive number of occurrences of a delimiter

See the following example:

SELECT 
    SUBSTRING_INDEX('Hello World', 'l', 1);
Code language: SQL (Structured Query Language) (sql)

In this example, the delimiter is l and the n is 1, therefore, the function returns every character up to the 1st occurrence of the delimiterl.

Here is the output:

+----------------------------------------+
| SUBSTRING_INDEX('Hello World', 'l', 1) |
+----------------------------------------+
| He                                     |
+----------------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

The following shows another example of using the SUBSTRING_INDEX() function:

SELECT 
    SUBSTRING_INDEX('Hello World', 'l', 2);Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------------------------+
| SUBSTRING_INDEX('Hello World', 'l', 2) |
+----------------------------------------+
| Hel                                    |
+----------------------------------------+
1 row in set (0.00 sec)        
Code language: SQL (Structured Query Language) (sql)

And

SELECT 
    SUBSTRING_INDEX('Hello World', 'l', 3);Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------------------------+
| SUBSTRING_INDEX('Hello World', 'l', 3) |
+----------------------------------------+
| Hello Wor                              |
+----------------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

2) Using SUBSTRING_INDEX() function with a negative number of occurrences of a delimiter

See the following example:

SELECT 
    SUBSTRING_INDEX('Hello World', 'l', -1);Code language: SQL (Structured Query Language) (sql)

In this example, the delimiter is l and the n is -1, therefore, the function returns every character from the right of the string up to the 1st occurrence of the character l, (counting from the right)

Here is the output:

+-----------------------------------------+
| SUBSTRING_INDEX('Hello World', 'l', -1) |
+-----------------------------------------+
| d                                       |
+-----------------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

Here is another example:

SELECT 
    SUBSTRING_INDEX('Hello World', 'l', - 2) result1,
    SUBSTRING_INDEX('Hello World', 'l', - 3) result2;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+----------+
| result1 | result2  |
+---------+----------+
| o World | lo World |
+---------+----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

3) Using SUBSTRING_INDEX() function with the table data example

See the following customers table from the sample database:

This example uses the SUBSTRING_INDEX() function to extract the house numbers from the addresses of all customers in the USA:

SELECT 
    customerName,
    addressLine1,
    SUBSTRING_INDEX(addressLine1, ' ', 1) house_no
FROM
    customers
WHERE
    country = 'USA'
ORDER BY 
    customerName;Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

In this tutorial, you have learned how to use the MySQL SUBSTRING_INDEX() function to get a substring from a string before a specified number of occurrences of the delimiter.