Extract certain text of a string using mysql

Question

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.

So the first part is always exactly this, `https://scontent-sjc3-1.xx.fbcdn.net/v/t1.0-1/p200x200/`? Then you will only need a basic SUBSTRING call, because the length of the part you want to remove is already known to you ... — CBroe, Feb 08 '18 at 12:24
Please edit the question to provide proper specs (is the string always `https://scontent-sjc3-1.../68_n.png`? can't it ever be e.g. `https://scontent-sjc3-1.../69_n.png`?) and show your attempts. This is not a free code writing service ;-) — Álvaro González, Feb 08 '18 at 12:24
We don't change the titles of questions when they are satisfactorily answered. The point of SO is to create a treasure trove of questions for other programmers. — O. Jones, Feb 08 '18 at 13:24

score 0 · Answer 1 · answered Feb 08 '18 at 12:25

You can try something like this

SELECT DISTINCT post_content, LEFT(post_content, LENGTH(post_content) - 36) as gallery_content_post, 
    post_title, substring(post_content,12, 1) as gallery_id
    FROM wp_posts
    WHERE id=[some id]

Hope this will work

score 0 · Accepted Answer · answered Feb 08 '18 at 12:26

0

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.

answered Feb 08 '18 at 12:26

O. Jones

103,626
17
118
172

I adapted this and worked like a charm. Thanks for sharing this content, it will be very useful to me – Gabriel Arcari Feb 08 '18 at 12:33

Extract certain text of a string using mysql

2 Answers2

You can try something like this