find and replace from another table mysql

Question

I need to find and replace multiplie strings from table "phrases" using table "dict"

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)

pharses table example:

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

dict table example:

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

I need get to phares like this:

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

It will replace only 1 string at once in row, but I have about 3-10 strings to replace.

How this code can be changed to replace all strings in rows?

possible duplicate of [Find and replace string in MySQL using data from another table](http://stackoverflow.com/questions/2509835/find-and-replace-string-in-mysql-using-data-from-another-table) — fancyPants, Sep 22 '14 at 10:01

score 1 · Answer 1 · answered Sep 09 '15 at 05:18

Create function and use it for 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;

the final update will be:

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

Markus · Answer 2 · 2014-09-22T11:24:05.387

maybe not a very nice 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//

problem with this is it will replace comput with computerer because there is comput in computer so it's replaced twice

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 — user2421781, Sep 22 '14 at 12:40
used that syntax to check in sqlfiddle http://sqlfiddle.com/#!2/3d919b, you probably have to change // with ; at the end — Markus, Sep 23 '14 at 05:05

Akhil · Answer 3 · 2014-09-22T13:58:55.870

0

Try This

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 not a smooth way. But definitely in a single query. Try running the inner query separately to figure out how it works!

edited Sep 22 '14 at 13:58

answered Sep 22 '14 at 13:23

Akhil

2,602
23
36

#1052 - Column 'id' in field list is ambiguous – user2421781 Sep 22 '14 at 13:30
Edited the answer.. Pls try now – Akhil Sep 22 '14 at 13:59
#126 - Incorrect key file for table '/tmp/#sql_1889_3.MYI'; try to repair it – user2421781 Sep 22 '14 at 15:56
Try this query with sample tables with few records to understand how it workks. Later you can use it for bigger tables with right set of indexes. – Akhil Sep 23 '14 at 05:59

score 0 · Answer 4 · edited Sep 09 '15 at 05:49

0

I think this will 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 phrases table that match the dict.source field with its corresponding dict.translate.

edited Sep 09 '15 at 05:49

Praveen

8,945
4
31
49

answered Apr 06 '15 at 08:00

Collin Lo

16
2

Could you please explain more your solution – abarisone Apr 06 '15 at 08:09
Added a brief detail on how my proposed solution work. – Collin Lo Apr 07 '15 at 01:02

find and replace from another table mysql

4 Answers4