从另一个表中查找和替换mysql

Question

我需要使用表"dict“从表”短语“中查找和替换多个字符串。

我的代码如下：

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

pharses表示例：

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

字典表示例：

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

我需要像这样的步骤：

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

它一次只能替换一行中的1个字符串，但我有大约3-10个字符串要替换。

如何更改此代码以替换行中的所有字符串？

Answer 1

用于更新的Create函数

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;

的最终更新将是：

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

Answer 2

也许不是一个很好的解决方案，但至少有一个

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//

这个的问题是它将替换为计算机，因为计算机中有计算机，所以它被替换了两次

Answer 3

尝尝这个

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;

这并不是一种顺利的方式。但绝对是在一个查询中。尝试单独运行内部查询，了解它是如何工作的！