Get the index of last substring in a given string in MySQL?

To get the index of last substring in a given string, use the char_length() function. First, we need to calculate string length and subtract the last sub string length from the entire length. The difference in length is index of substring.

Syntax

The syntax is as follows −

select CHAR_LENGTH(yourColumnName) - LOCATE('yourDelimiter ',
REVERSE(yourColumnName))+1 as anyVariableName
from yourTableName;

To understand the above syntax, let us first create a table. The query to create a table is as follows −

mysql> create table SubStringIndexDemo
   -> (
   -> Words varchar(200)
   -> );
Query OK, 0 rows affected (0.57 sec)

Insert some records in the table using the insert command. The query is as follows −

mysql> insert into SubStringIndexDemo values('This is MySQL Query');
Query OK, 1 row affected (0.13 sec)

mysql> insert into SubStringIndexDemo values('MySQL is a Relational Database');
Query OK, 1 row affected (0.34 sec)

mysql> insert into SubStringIndexDemo values('Java is a programming language');
Query OK, 1 row affected (0.11 sec)

mysql> insert into SubStringIndexDemo values('Spring is a Framework');
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select command. The query is as follows −

mysql> select *from SubStringIndexDemo;

Output

+--------------------------------+
| Words                          |
+--------------------------------+
| This is MySQL Query            |
| MySQL is a Relational Database |
| Java is a programming language |
| Spring is a Framework          |
+--------------------------------+
4 rows in set (0.00 sec)

Here is the query that can be used to get the index of the last substring::

mysql> select CHAR_LENGTH(Words) - LOCATE(' ', REVERSE(Words))+1 as
PositionOfLastSubstring
   -> from SubStringIndexDemo;

Output

+-------------------------+
| PositionOfLastSubstring |
+-------------------------+
|                      14 |
|                      22 |
|                      22 |
|                      12 |
+-------------------------+
4 rows in set (0.00 sec)

George John

e

Updated on: 25-Jun-2020

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started