I have several rows with a lot of content and i'm trying to find some code and extract it. It looks like this:
Some code... <div> </div> <div>[flv:http://submarino-libre.s3.amazonaws.com/opciones/basico/webready/Video1CursoOpcionesbasicoWebReady.mp4 http://www.submarinobursatil.com/imagenes/portadas/curso-de-opciones.png 600 369]</div> <div align="left">Duración: 09:56</div> Some other code...
Figured out that I need to find
[flv: and extract all the line (2 links) ending with
So i came out with this query:
SELECT SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`)) AS amazonlinks FROM posts
First LOCATE function works fine, it finds
[flv: but second LOCATE functions doesn't stop the query extraction on
]. It gets a lot more code from the next lines. It only works when i set a number but there is no way i can know the exact lenght number.
Is it possible to use 2 LOCATE functions as parameters of SUBSTRING ? Or what am i'm doing wrong?
UPDATE: Adding DBFiddle: https://www.db-fiddle.com/f/m62LBUStp1UbRCk2eVNRrY/0