how can i implement this concept using mysql ?

Question

My aim is to make a procedure that take a text -may contain idioms- as an input and it is output should be that text after replacing each idiom with it's meaning that their is a table called "idioms" which has two columns : the first is for idiom called "idiom" and the second is for idiom meaning and called "idiomMeaning" and this is my work till now :

delimiter //
create procedure idioms_search (in input text, out output text )

begin

   WHILE EXISTS (SELECT idiom  FROM idioms  WHERE input LIKE CONCAT('%',@idiom,'%');)  

       SELECT REPLACE (@input,@idiom,@idiomMeaning ) INTO output;

end while;

end

//

delimiter ;

but the previous code contain errors , Any suggestions ?

Answer 1

There is no loop called while exists in mysql. The loop is coded like the example given below

while x<=5 do
select x;
end while;`

try and use cursors. Rephrase your select statement as given below and try

SELECT idiom  FROM idioms  WHERE idiom like concat('%',input,'%');