Get left part of the string on the basis of last occurrence of delimiter in MySQL?

For this, use LEFT() method. For manipulation, we have used the LOCATE() and the REVERSE() method.

Let us first create a table −

mysql> create table DemoTable
(
   Title text
);
Query OK, 0 rows affected (0.52 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('$/This$is[MySQL]$/MySQL[FirstClass]$MySQL[SecondClass]');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('$/This$is[Java]$/Java[FirstClass]$Java[SecondClass]');
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------------------------------------------------------+
| Title                                                  |
+--------------------------------------------------------+
| $/This$is[MySQL]$/MySQL[FirstClass]$MySQL[SecondClass] |
| $/This$is[Java]$/Java[FirstClass]$Java[SecondClass]    |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

Following is the query to get left part of the string −

mysql> select left(Title, char_length(Title) - locate('$', reverse(Title))) as result from DemoTable;

This will produce the following output −

+-------------------------------------+
| result                              |
+-------------------------------------+
| $/This$is[MySQL]$/MySQL[FirstClass] |
| $/This$is[Java]$/Java[FirstClass]   |
+-------------------------------------+
2 rows in set (0.04 sec)

AmitDiwan

Published on 07-Oct-2019 11:42:00

• Related Questions & Answers
• Split the left part of a string by a separator string in MySQL?
• Get the last occurrence of a substring within a string in Arduino
• Get part of a string based on a character in MySQL?
• Get Last Part of URL in PHP?
• How to get everything before the last occurrence of a character in MySQL?
• How to get the last index of an occurrence of the specified value in a string in JavaScript?
• Get the substring before the last occurrence of a separator in Java
• Search records on the basis of date in MySQL?
• Add a column count in a MySQL query on the basis of last name records?
• Need to populate autocomplete with records on the basis of first and last name of schools in MySQL?
• Fetch records on the basis of LastName using MySQL IN()
• Finding the last occurrence of a character in a String in Java
• Get the index of last substring in a given string in MySQL?
• Get the last days of all the months in MySQL?
• Delete a specific record on the basis of EmployeeId in MySQL