Find and replace from another mysql table

Question

I need to find and replace multiple lines from tabular "phrases" using the "dict" table

I have code like:

update phrases, dict set phrases.name = replace(phrases.name, dict.source, dict.translate) where phrases.name <> replace(phrases.name, dict.source, dict.translate) 

An example pharses table:

 id | name | .. | .. 1 | macbook wht comput | .. 2 | lenova blck god nb | .. 

Example dict table:

 id | source | translate 1 | wht | white 2 | god | good 3 | lenova | lenovo 4 | blck | black 5 | comput | computer 6 | nb | notebook 

I need to get lights like this:

 id | name | .. | .. 1 | macbook white computer | .. 2 | lenova black good notebook | .. 

It will replace only one line in a row, but I have about 3-10 lines to replace.

How can this code be changed to replace all the lines in the lines?

Answer 1

Create a function and use it to update

 CREATE OR REPLACE FUNCTION translate_phrases_name(phraseId numeric) RETURNS character varying AS $BODY$ DECLARE phrasesString character varying; newPhrasesString character varying; currentWord character varying; currentWordTranslation character varying; i numeric; wordsCount numeric; BEGIN phrasesString := (select name from phrases where id = phraseId); --the string that u want to get, we will use it later newPhrasesString := phrasesString; phrasesString := trim(phrasesString); phrasesString := regexp_replace(phrasesString, '\s+', ' ', 'g'); wordsCount := length(regexp_replace(phrasesString, '[^ ]+', '', 'g')); --the count of the words is +1 more than count of spaces wordsCount := wordsCount + 1; --working with each word for i in 1..wordsCount loop --find first word in string currentWord := substring(phrasesString from '\A[^ ]+'); --find translation in dict table currentWordTranslation := (select translate from dict where source = currentWord); --constructing string that u want newPhrasesString := replace(newPhrasesString, currentWord, currentWordTranslation); --kill first word for next iteration of loop phrasesString := replace(phrasesString, currentWord, ''); end loop; return newPhrasesString; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION translate_phrases_name(numeric) OWNER TO postgres; 

last update will be:

 update phrases set name = (select translate_phrases_name(id)); 

Answer 2

maybe not a good solution, but at least one

 CREATE PROCEDURE proc_replaceFromTable() BEGIN DECLARE countRowsDict int; DECLARE countRowsEpl int; DECLARE currDict int; DECLARE currExample int; DECLARE d_source varchar(255); DECLARE d_translate varchar(255); SELECT count(id) into countRowsDict from dict; SELECT count(id) into countRowsEpl from pharses; SET currDict = 0; SET currExample = 0; WHILE currExample < countRowsEpl DO SET currDict = 0; WHILE currDict < countRowsDict DO SELECT source INTO d_source FROM dict LIMIT currDict, 1; SELECT translate INTO d_translate FROM dict LIMIT currDict,1; UPDATE pharses SET text = REPLACE(text, d_source, d_translate); SET currDict = currDict + 1; END WHILE; set currExample = currExample + 1; END WHILE; END// 

the problem with this is that it will replace comput with computerer, because the computer is being computed, therefore it is being replaced twice

Answer 3

try it

 UPDATE phrases, (SELECT id, replaced FROM ( SELECT (@cntr := @cntr + 1) cnt, id, @temp := REPLACE(COALESCE(IF(@tempID <> ID, NULL, @temp), NAME), source, translate) replaced, @tempID := ID FROM ( SELECT @cntr := 0, @tempID := 0, @temp := NULL, phrases.id, NAME, source, translate FROM phrases, dict ORDER BY ID DESC ) a ORDER BY cnt DESC ) b GROUP BY ID DESC ) derivedTable SET phrases.name = derivedTable.replaced WHERE phrases.id = derivedTable.id; 

This is behind the scenes. But definitely in one request. Try the internal query separately to find out how it works!

Answer 4

I think this will solve your problem.

 DECLARE @DictId INT DECLARE @MaxId INT SET @DictId = 1 SELECT @MaxId = MAX(id) FROM dict DECLARE @Source NVARCHAR(MAX) DECLARE @Translate NVARCHAR(MAX) WHILE (@DictId <= @MaxId) BEGIN SELECT @Source = source ,@Translate = translate FROM dict WHERE id = @DictId UPDATE pharses SET name = REPLACE(name,@Source,@Translate) SET @DictId = @DictId + 1 END 

What this script will do is iterate through the dict table and replace any words found in the phrase table that match the dict.source field with the corresponding dict.translate.