mysql: substring that returns the first occurance from the right ? (subrstring?!)

Question

Hi. is there a way to return the first occurrence of a space from the right side of the string in sql ?

thanks! :)

Answer 1

I think you are looking for something like SUBSTRING_INDEX

mysql> SELECT SUBSTRING_INDEX('first second end', ' ', -1);
+----------------------------------------------+
| SUBSTRING_INDEX('first second end', ' ', -1) |
+----------------------------------------------+
| end                                          |
+----------------------------------------------+
1 row in set (0.00 sec)

Answer 2

You could use REVERSE in conjunction with INSTR.

i.e.

select right('12345 67 8', instr(reverse('12345 67 8'), ' '));

returns '8'.

Answer 3

Hmm, a brief browse through the function list didn't pop up any "search backwards" functions at me, but what you could do is reverse the string and search forwards:

SELECT LENGTH(`haystack`) - POSITION('needle' IN REVERSE(`haystack`))