EN VI

MySQL Substring_Index with negative numbers?

How to MySQL Substring_Index with negative numbers

I need help with a substring_index query capable of selecting both negative and positive numbers from a string.

Two example strings:

-25-10/-23-10/-22-10

25-10/23-10/22-10

In the first string I would like -22 and the second string 22 to be returned.

The following works when the result is positive:

SET @test := '25-10/23-10/22-10';

SELECT substring_index(substring_index(@test,'/',-1),'-',1);

returns 22

I have not been able to figure it out for negative, let alone work for both positive and negative.

Any ideas?

Solution:

Don't use SUBSTRING_INDEX() for this, since it can't distinguish the - delimiter from the one being used to indicate negative numbers.

After you extract the substring after the last /, convert it to a number. This will parse the beginning of the string as a number, and ignore everything after that. So -22-10 will be parsed as -22.

mysql> SET @test := '25-10/23-10/22-10';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 0 + substring_index(@test,'/',-1);
+-----------------------------------+
| 0 + substring_index(@test,'/',-1) |
+-----------------------------------+
|                                22 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> set @test := '-25-10/-23-10/-22-10';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 0 + substring_index(@test,'/',-1);
+-----------------------------------+
| 0 + substring_index(@test,'/',-1) |
+-----------------------------------+
|                               -22 |
+-----------------------------------+
1 row in set (0.00 sec)