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>[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

  • Yes, that's possible. Create a minimal dbfiddle (db-fiddle.com) that demonstrates the problem and add the link to your question. Jul 28, 2019 at 11:05
  • Updated and added the dbfiddle. As you can see it's not ending at ] Thanks in advance Jul 28, 2019 at 11:36

1 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
                -- number of characters from offset
                ,LOCATE(']',`content`) - LOCATE('[flv:',`content`) + 1 ) 
FROM amazonlinks

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

  • Thanks a lot mate. Didn't know that :) Jul 28, 2019 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.