1

i want to replace many keywords with only one replace statement in mysql select query.

my table is like

enter image description here

select replace(Text,' ',' ');

i want to replace the matching keywords with concat('_' , Keywords , '__').

Need help.

cacti5
  • 2,006
  • 2
  • 25
  • 33
Sukhvindra Singh
  • 200
  • 2
  • 14
  • 1
    I can't think of any easy way to do this in SQL. You could use a loop in a stored procedure. – Barmar Jun 15 '18 at 18:16
  • Can you show the result you're trying to get? – Barmar Jun 15 '18 at 18:18
  • i am trying like select replace('In a cheering news for the beleaguered news industry','news,industry,for','working'); and getting the same string "In a cheering news for the beleaguered news industry". because the system is searching for complete "news,industry,for" string but i want to search for all three individual words. – Sukhvindra Singh Jun 15 '18 at 19:09
  • 1
    In general, putting comma-separated lists in SQL tables is a bad idea. There's no easy way to split it up and work with each word. There's also no easy way to do a variable number of replacements on a string in a single query. You'll probably need to write a stored procedure or do this in a client language. – Barmar Jun 15 '18 at 19:27
  • I'd still like you to edit the question and show the result you're trying to achieve. – Barmar Jun 15 '18 at 19:28
  • My assumption is you're trying to get `In a cheering _news_ _for_ the beleaguered _news_ _industry_` but you should make it clear. – Barmar Jun 15 '18 at 19:28
  • Don't forget that `replace()` doesn't know anything about word boundaries. If you replace `for` with `_for_`, it will turn `inform` to `in_for_m`. – Barmar Jun 15 '18 at 19:30
  • This kind of thing would be better to do with a regexp replacement function in a client language, then you can use `\b` to match word boundaries around the keyword. – Barmar Jun 15 '18 at 19:31
  • See https://stackoverflow.com/questions/1671040/can-mysql-replace-multiple-characters for MySQL. As @Barmar said, because of your data format there's not a great solution – cacti5 Jun 15 '18 at 20:48

1 Answers1

1

You can try like this. This can be helpful.

CREATE FUNCTION `SPLIT_STR`(
  x text,
  delim VARCHAR(12),
  pos INT
) RETURNS varchar(255) CHARSET utf8
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '')


CREATE  FUNCTION `GDPR_find_keyword_and_color_them`(qtntext text,keyword nvarchar(50000)) RETURNS text CHARSET utf8
BEGIN

declare ttlkeyword bigint;
declare loopvar bigint;
declare keyword_ nvarchar(1000);
set ttlkeyword=(select count(*) from nsmx_beta4.privacy_policy_keywords);

set loopvar=1;
                while(loopvar<ttlkeyword) do
                                set keyword_=(select SPLIT_STR(keyword,',',loopvar));
                                if(keyword_!='')  then
                                                set qtntext=replace(qtntext,keyword_,concat('<b>',keyword_,'</b>'));
                                end if;
                                set loopvar=loopvar+1;
                end while;    
return qtntext;
END

select GDPR_find_keyword_and_color_them('hello my name is','my,is');

result is : hello my name is

Jay Prakash
  • 92
  • 1
  • 8