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

Question

我需要使用表“dict”从表“phrases”中查找并替换多个字符串

我有这样的代码:

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

短语表示例:

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

我需要像这样进入phares:

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

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

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

Answer 1

创建函数并将其用于更新

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));