Geek asks and answers

Mysql substring_index reverse

in MYSQL, given a line with dots in it, I want to select everything to the last point. 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?

+3
source share
3 answers
    select substr('www.java2s.sth.com', 1, (length('www.java2s.sth.com')- 
    length(SUBSTRING_INDEX(('www.java2s.sth.com'), '.', -1))-1));`
+3
source

If you are looking for a nicer solution, this is:

SET @domain = "www.java2s.com";
SELECT SUBSTRING(@domain, 1, LENGTH(@domain)-LOCATE('.', REVERSE(@domain)));
+2
source

use this

SELECT SUBSTRING_INDEX('www.java2s.com', '.', 2)

more general

SELECT SUBSTRING_INDEX('www.java2s.com', '.', ( LENGTH('www.java2s.com') - LENGTH(REPLACE('www.java2s.com', '.', '')))  )
0
source

More articles:


All Articles