SQL Replacement via table

I've got a table A with a column full of sentences.

I've got another table B with two columns: words and abbreviations.

I want to look through table A's column sentences and if a word from table B's word colum开发者_运维百科n matches then replace it with abbreviation.

Hope that is clear.

Case doesn't matter, I can deal with that. Assume everything is lower or upper or whatever.

---

You can't do this with SQL alone, you'd need to pull the data from the database, manipulate it and then push it back.

There's a bunch of ways to do it, some are simpler that others and some are more efficient.

For example a simple but slow method would be (in pseudocode)...

sentence_list = db.execute("SELECT id, sentence FROM A")
for sentence in sentence_list do
    words = tokenize(sentence.text)

    for word in words do
        abbrev = db.execute("SELECT abbrev FROM B WHERE word=word")
        if abbrev 
            word = abbrev

    sentence.text = concat(words)
    db.execute("UPDATE A SET sentence=" + sentence.text + " WHERE id = " + sentence.id + ")")

That's doing a query for every word in every sentence and not recommended for performance critical situations but it does the job.

---

I know it's an old question but since there is no answer, I'll give a try with this :

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Table1
    (`Id` int, `Sentence` varchar(80))
;

INSERT INTO Table1
    (`Id`, `Sentence`)
VALUES
    (1, 'Mister John is going to Los Angeles')
;

CREATE TABLE Table2
    (`Id` int, `Word` varchar(60), `Abbrev` varchar(10))
;

INSERT INTO Table2
    (`Id`, `Word`, `Abbrev`)
VALUES
    (1, 'Mister', 'Mr.'),
    (2, 'Los Angeles', 'L.A.')
;


DROP PROCEDURE IF EXISTS updateSentences //

CREATE PROCEDURE updateSentences()
BEGIN

  DECLARE count INT;

  SELECT COUNT(*) INTO count 
   FROM Table1 
   INNER JOIN Table2 ON CONCAT(' ',Sentence,' ') LIKE CONCAT('%',Word,'%');

  WHILE count > 0 DO
    UPDATE Table1 
    INNER JOIN (SELECT t1.id, Word,Abbrev
            FROM Table1 t1
            INNER JOIN Table2 ON CONCAT(' ',Sentence,' ') LIKE CONCAT('%',Word,'%')
            LIMIT 1) Table2 ON Table1.Id = Table2.Id
    SET Sentence = REPLACE(Sentence,Word,Abbrev);
    SELECT COUNT(*) INTO count 
     FROM Table1 
     INNER JOIN Table2 ON CONCAT(' ',Sentence,' ') LIKE CONCAT('%',Word,'%');
  END WHILE;
END//

Query:

SELECT *
FROM Table1

[Results]:

| ID |                            SENTENCE |
|----|-------------------------------------|
|  1 | Mister John is going to Los Angeles |

Query:

CALL updateSentences()

SELECT *
FROM Table1

Results:

| ID |                  SENTENCE |
|----|---------------------------|
|  1 | Mr. John is going to L.A. |

---

You could get crazy and add a mysql odbc connection in excel. Query the sentences in one query table, Query the lookup words in another table, and write a little macro that refreshes the tables then does a find and replace, then re-import it back into your table. I know in Ms sql, you can do all this automatically in DTS/SSIS.