Search code examples
mysqlurlstring-parsingurl-parsing

Extract certain text of a string using mysql

I have the following string:

https://scontent-sjc3-1.xx.fbcdn.net/v/t1.0-1/p200x200/26056120_2094108927485604_7093424189760207868_n.png

What I want is to keep only:

26056120_2094108927485604_7093424189760207868_n.png

I already tried using LOCATE and INSTR, but without any success. Some people told me to use regex, but I don't know how to do it in MySQL.

All I need is info about the correct path to do this.

PS: 1 - I need to UPDATE 2 - REPLACE isnt an option, I have another facebook strings that are different.

Thanks.

Solution

  • You want, it seems, everything after the last / in each URL string. This is a job for SUBSTRING_INDEX() with a negative last parameter.

     SELECT SUBSTRING_INDEX(url_column, '/', -1)

    will do the trick.

    When I was six monhths into learning MySQL, I found it very helpful to take two hours to reread these two manual pages.