Use SUBSTRING_INDEX syntax on MySQL database

Question

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 ?

For me, it works for the first example when using -2 instead of -1. Is there any rule behind this algorithm? — Nico Haase
– Nico Haase, Commented Jan 9, 2019 at 11:23

Nitin Kumar Singh · Accepted Answer · 2019-01-09 11:41:56Z

2

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

answered Jan 9, 2019 at 11:41

Nitin Kumar Singh

3223 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use SUBSTRING_INDEX syntax on MySQL database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related