user121196 user121196 - 7 months ago 44
SQL Question

mysql substring_index reverse

in MYSQL, given a string with dots in it, I want to select everything before the last dot. for example:
input


www.java2s.com


output:


www.java2s


substring_index seems to do the opposite:


mysql> SELECT SUBSTRING_INDEX('www.java2s.com', '.', -1);
+--------------------------------------------+
| SUBSTRING_INDEX('www.java2s.com', '.', -1) |
+--------------------------------------------+
| com |
+--------------------------------------------+


can this be done?

Answer
    select substr('www.java2s.sth.com', 1, (length('www.java2s.sth.com')- 
    length(SUBSTRING_INDEX(('www.java2s.sth.com'), '.', -1))-1));`