MySQL String Last Index Of

Question

I am able to use LOCATE to get the index of a character such as a . in www.google.com. Thus I can use substring to strip out everything before the first ..

Is there a way I can look for the last /?

So I can get test.htm out of http://www.example.com/dev/archive/examples/test.htm?

I do not want to say the 6th slash, I want to say the last slash. Can this be done?

Chiliarch

Answer 1

Use substring_index

select substring_index('http://www.example.com/dev/archive/examples/test.htm','/',-1)

Remotion

@Conrad: You would have received a +1 from me anyway for creative thinking if you had not deleted your answer :) – Chiliarch

Never used substring_index before... my new favorite function. – Nardi

Glad you find useful my post. ;) – Remotion

Is there a way to get the index of that point and not just the text after that point? – Organotherapy

@mike you could try select length(URL) - length(substring_index(URL, '/', -1));, where 'URL' is the string or name of the column you're working on ... it worked for me. dev.mysql.com/doc/refman/5.7/en/string-functions.html Note that there are also 'char_length' and 'bit_length' functions if you are concerned with multibyte characters and the like, but I'm guessing the results would be consistent as long as you're using the same function to get the length in both instances. – Burris