1

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

  • Yes, that's possible. Create a minimal dbfiddle (db-fiddle.com) that demonstrates the problem and add the link to your question. – Lennart Jul 28 '19 at 11:05
  • Updated and added the dbfiddle. As you can see it's not ending at ] Thanks in advance – gallo2000sv Jul 28 '19 at 11:36
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

| improve this answer | |
  • Thanks a lot mate. Didn't know that :) – gallo2000sv Jul 28 '19 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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