Using LOCATE() Function as SUBSTRING() parameter gives ramdom result. Extracting string from text

Question

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>&nbsp;</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&oacute;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

Answer 1

Substrings first argument is the offset, the second argument is the number of characters from the offset. You need to subtract the offset from the second parameter:

SELECT title
     , SUBSTRING(`content`
                -- offset in string
                ,LOCATE('[flv:',`content`)
                -- number of characters from offset
                ,LOCATE(']',`content`) - LOCATE('[flv:',`content`) + 1 ) 
FROM amazonlinks

I added +1 to include "]" in the result