SQL Replacement via table

Question

Mysql question:

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 column 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.

Answer 1

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.

Answer 2

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.

Answer 3

Don't know if you really want to, but you should be able to do this purely in SQL

UPDATE Sentence INNER JOIN Word ON sentence.sentence like '%' + Word.WORD + '%' SET sentence = replace(sentence, word, abvr)

(Code not checked, may have mistakes).