Mass-replacing strings in MySQL

mysql, sql

Solution

You could try storing the list of tickers and companies in a table and use a query like this:

select
    case
        when c.ticker is not null then
            replace(t.tweet, c.name,
                    concat('{COMPANY|',c.ticker,'}'))
        else t.tweet
    end as tweet
from
    tweets t
left join
    company c
        on
            t.tweet like concat('% ', c.name, ' %')
        or
            t.tweet like concat(c.name, ' %')
        or
            t.tweet like concat('% ', c.name)
group by
    t.id

The only downside being this solution won't handle the case where two companies' names appear in the same tweet. Only one of them will get replaced.

Demo: http://www.sqlfiddle.com/#!2/8da9d/2

Edit: As pointed out by `@Mario`, it's possible that the matching will cause an incorrect replacement if there are multiple instances in the same string. For instance, using the string `I have a redapple from apple` with the replacement `apple` will result in the string `I have a red{COMPANY|AAPL} from {COMPANY|AAPL}` because the second `apple` triggers a `LIKE` match, then the `REPLACE` replaces every match. I have an updated query that handles this situation as well by reading the matching strings out of a table as well:

select
    case
        when p.ticker is not null then
            replace(t.tweet,
                replace(p.replacestr, '{0}', p.name),
                replace(p.replacestr, '{0}', concat('{COMPANY|',p.ticker,'}')))
        else t.tweet
    end as tweet
from
    tweets t
left join
    (select * from pattern,company) p
        on t.tweet like replace(p.pattern, '{0}', p.name);
group by
    t.id

Where `Pattern` is a table defined like this:

CREATE TABLE Pattern
    (pattern varchar(50), replacestr varchar(50));

INSERT INTO Pattern
    (pattern, replacestr)
VALUES
    ('% {0} %', ' {0} '),
    ('{0} %', '{0} '),
    ('% {0}', ' {0}');

Demo: http://www.sqlfiddle.com/#!2/c71d4/3

Problem

Original source