mysql substring_index reverse

Question

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?

Afshin Moazami · Accepted Answer · 2012-05-19 19:37:07Z

6

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

edited May 19, 2012 at 19:37

answered May 19, 2012 at 5:51

Afshin Moazami

2,0985 gold badges33 silver badges57 bronze badges

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

Comments

dgpro · Accepted Answer · 2016-04-29 17:17:21Z

6

If you are looking for a nicer solution here it is:

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

answered Apr 29, 2016 at 17:17

dgpro

3653 silver badges10 bronze badges

Comments

Satya · Accepted Answer · 2012-05-19 05:18:02Z

1

use this

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

more generic

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

edited May 19, 2012 at 5:18

answered May 19, 2012 at 5:07

Satya

8,9016 gold badges41 silver badges57 bronze badges

1 Comment

user121196 Over a year ago

the problem is that I don't know how many dots are in the file, I don't know it's 2 or x

Collectives™ on Stack Overflow

mysql substring_index reverse

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related