Use SUBSTRING_INDEX syntax on MySQL database

On my MYSQL table I have these string :

24015902-C-72
24018504-147

I need extract these output :

24015902 C-72  
24018504 147

And I have tried this query :

mysql> SELECT
    SUBSTRING_INDEX('24015902-C-72', '-', 1) AS ResultString1,
    SUBSTRING_INDEX('24015902-C-72', '-', - 1) AS ResultString2,
    SUBSTRING_INDEX('24018504-147', '-', 1) AS ResultString3,
    SUBSTRING_INDEX('24018504-147', '-' ,- 1) AS ResultString4;
+---------------+---------------+---------------+---------------+
| ResultString1 | ResultString2 | ResultString3 | ResultString4 |
+---------------+---------------+---------------+---------------+
| 24015902      | 72            | 24018504      | 147           |
+---------------+---------------+---------------+---------------+
1 row in set

mysql> 

But on this string 24015902-C-72 the value of C- is lost.

How to do resolve this ?

Answer

SUBSTRING_INDEX(string, delimiter, number)

  • string: The original string

  • delimiter : The delimiter to search for

  • number: The number of times to search for the delimiter. Can be both a positive or negative number. If it is a positive number, this function returns all to the left of the delimiter. If it is a negative number, this function returns all to the right of the delimiter.



In our case it should work like this for the second statement :

SELECT SUBSTRING_INDEX("24015902-C-72", "-", -2);