multiple substring in a string mysql

Question

the fulltext field contains html tags and some resources and I want to get all 'src' attributes from that. some records maybe have several 'src'.

now, I dont know how I can write a query to obtain the desired result. and this is my code

DROP FUNCTION IF EXISTS SPLIT_STR;
CREATE FUNCTION SPLIT_STR(
     x text,
     delim VARCHAR(10),
     co INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
    WHILE co >= 0 DO
         SET x = RIGHT(x, ( (CHAR_LENGTH(x)) - (InStr(x,'src'))  + 1 ));
         RETURN SUBSTR(x, 1, 40);
    END WHILE;
END

#---------------------------------------------------
SELECT id, SPLIT_STR(test.fulltext, 'src',ROUND (   
    (
        LENGTH(test.fulltext)
        - LENGTH( REPLACE ( test.fulltext, "src", "") ) 
    ) / LENGTH("src")        
))
FROM test
WHERE test.fulltext LIKE '%src%'

and the result should be like this result

Don't save CSV in a column http://stackoverflow.com/questions/41304945/best-type-of-indexing-when-there-is-like-clause/41305027#41305027 http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry/41215681#41215681 — e4c5, May 31 '17 at 09:16

score 0 · Answer 1 · answered May 31 '17 at 09:42

0

Function ExtractValue works with xml in mysql. So, you can use this:

SELECT ExtractValue(test.fulltext, '//img/@src')
FROM test
WHERE test.fulltext LIKE '%src%'

answered May 31 '17 at 09:42

Timur

488
4
14

Although that might work, does it do what the person is requesting? If the incoming string has 5 "src" values, they want each one, not just an extraction of a single instance that may be embedded? – DRapp May 31 '17 at 10:46
@DRapp Yes, it'll return one string that contains all "src" attributes separated by space. But that instance better than author's suggestion. It no need use any custom function and for splitting for instances it need only `SPLIT_STR` – Timur May 31 '17 at 11:22

multiple substring in a string mysql

1 Answers1