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

functionsMySQLstring manipulationstring-searchingsubstring

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

Best Answer

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

Related Solutions

MySQL function to return breadcrumb-like string from hierarchical structure

You can use the following function for returning Hierarchical structure for N no:of leaf nodes like "World > Europe > France > S1 > S2"

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`test` $$ 
CREATE FUNCTION `test`.`test`(V_Argument_Parent_ID integer) 
RETURNS varchar(1024) No
SQL BEGIN

  Declare Done Integer default 0;   
  Declare V_id Integer default 0;  
  Declare V_parent_id Integer default 0;

  Declare V_name varchar(1024) default '';   
  Declare V_Result varchar(1024) default '';   
  Declare V_TempResult varchar(1024) default '';

  Declare continue handler for SQLWARNING set Done = 1;   
  Declare continue handler for NOT FOUND set Done = 1;   
  DECLARE EXIT HANDLER FOR 1001 set Done = 1;

  SELECT id, name, parent_id into V_id, V_name, V_parent_id 
  FROM a
  where ID = V_Argument_Parent_ID;

  IF V_parent_id = V_id then
    Set V_Result = V_name;   Else
    Set V_TempResult = ''; 
    Set V_TempResult = concat_ws('',V_TempResult,'->',V_name);
 
    MainLoop: Loop

     set V_Argument_Parent_ID = V_parent_id;
     SELECT id, name, parent_id into V_id, V_name, V_parent_id 
     FROM a where ID = V_Argument_Parent_ID;

     Set V_TempResult = concat_ws('',V_TempResult,'->',V_name);
     IF V_parent_id = V_id then
         Leave MainLoop;
     End IF;

    End Loop MainLoop;

    Set V_Result = V_TempResult;

    set V_Result = trim(both '->' from V_Result);
 
  End IF;
 
  Return V_Result;
 
 
END $$
 
DELIMITER ;

Try out

Mysql – join from different tables gives same result, but it shouldn’t

the solution was to add a subquery :D thanks!!

SELECT 
    `disenos`.*, 
    `usuario_contacto`.
    `nombres`, 
    `usuario_contacto`.
    `apellidos`, 
    `users`.`username`, 
    `users`.`avatar` as usuario_avatar, 
    `total_coment`, 
    COUNT(votos.id_usuario) as votos 
FROM (`disenos`) 
LEFT JOIN `votos` ON `votos`.`id_diseno` = `disenos`.`id_diseno` 
LEFT JOIN `users` ON `users`.`id_usuario` = `disenos`.`id_usuario` 

LEFT JOIN 
    ( 
        SELECT id_diseno, COUNT(id_comentario) as total_coment 
        FROM `comentarios` 
        GROUP BY `id_diseno` 
    ) `comments`

ON `comments`.`id_diseno` = `disenos`.`id_diseno`
LEFT JOIN `usuario_contacto` ON `usuario_contacto`.`id_usuario` = `disenos`.`id_usuario` 
WHERE `disenos`.`estado` = 1 
AND `disenos`.`votacion` = 1 
GROUP BY `disenos`.`id_diseno` 
LIMIT 5

EDIT: correcting a typo, sorry

Related Question