nesreen nesreen - 7 months ago 18
SQL Question

How to delete specific word from string in mysql?

I have string "this is my test string" and I want to remove only word "my".

The problem is word "my" will select from another query as

select word from table


I tried this if I know the word

set string = REPLACE(string,'my','');


So, any help

I want to do something like

set string = REPLACE(string,select word from table,'');


my query is

set @test = 'this is my test query';

DROP TABLE IF EXISTS `test`;

CREATE TEMPORARY TABLE IF NOT EXISTS test(word longtext );

insert into test values('my');
insert into test values('test');

select replace(@test,(select word from test),'');


I have got error of sub query should returns 1 row

Answer

select replace(@test,word,'') from test;

...

select @test:=replace(@test,word,'')  from test;

However keep in mind that you have multiple entries in the test table, @test will end up with all the words in table test replaced with ''. If that is what you want then all okay.

If you want a list output with @test with different words replaced use the first query and parse it in whatever you are passing the data to.