MySQL SUBSTRING_INDEX Function

Created with Sketch.

MySQL SUBSTRING_INDEX Function

Summary: in this tutorial, you will learn how to use the 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.

A) 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)

B) Using MySQL 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)

C) Using MySQL 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 for 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.

Leave a Reply

Your email address will not be published.