9

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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
user2421781
  • 399
  • 2
  • 5
  • 16

4 Answers4

1

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));
Ruslan Babich
  • 41
  • 1
  • 5
0

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

Markus
  • 693
  • 8
  • 23
  • #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
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!

Akhil
  • 2,602
  • 23
  • 36
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.

Praveen
  • 8,945
  • 4
  • 31
  • 49
Collin Lo
  • 16
  • 2