25

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?

700 Software
  • 85,281
  • 83
  • 234
  • 341

1 Answers1

70

Use substring_index

select substring_index('http://www.example.com/dev/archive/examples/test.htm','/',-1)
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
  • 1
    @Conrad: You would have received a +1 from me anyway for creative thinking if you had not deleted your answer :) – 700 Software Apr 26 '11 at 21:51
  • 2
    Never used substring_index before... my new favorite function. – OhkaBaka Jan 05 '12 at 21:15
  • Glad you find useful my post. ;) – Nicola Cossu Jan 05 '12 at 21:16
  • 6
    Is there a way to get the index of that point and not just the text after that point? – Mike Apr 24 '13 at 16:55
  • 1
    @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. http://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. – Aaron Wallentine Apr 19 '16 at 22:48