Is it possible to search and replace an array of strings in MySQL?

Question

Consider the following array:

"Bank of America"    => "BOA",
"Microsoft"          => "MSFT",
"Berkshire Hathaway" => "BRK"

In reality, the array is a 100 pairs long. If I have a MySQL table "strings" with text records:

str_id  str_text
-------------------------------------------------------------
1       I wish I had bought Berkshire Hathaway in the 1980's!
2       Microsoft to release Windows 8 in 4 flavours

Is there a way to - in SQL! - loop through all millions of records and replace all company names with their tickers? So that when the query is done, the strings table would look like this?

str_id  str_text
-------------------------------------------------------------
1       I wish I had bought BRK in the 1980's!
2       MSFT to release Windows 8 in 4 flavours

I'm asking since it is possible with PHP, but that would be inherently slower, since I'd have to retrieve a couple of thousand records, loop through them, update them, retrieve thousands more using crontab, etc.

Arunas · Accepted Answer · 2013-02-22 11:47:54Z

Alternative without PHP (MySQL only):

Create MySQL procedure:

    DELIMITER // 
    DROP PROCEDURE IF EXISTS replace_strings// 
    CREATE PROCEDURE replace_strings(IN table_name VARCHAR(100), IN field_name VARCHAR(100))

    BEGIN

    DECLARE done INT DEFAULT FALSE;
    DECLARE str_from VARCHAR(255);
    DECLARE str_to VARCHAR(255);
    DECLARE cur1 CURSOR FOR SELECT string_from, string_to FROM replacements;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO str_from, str_to;
        IF done THEN
            LEAVE read_loop;
        END IF;

    SET @sql_command = CONCAT("UPDATE ",table_name," SET ",field_name," = REPLACE(",field_name,", '",str_from,"', '",str_to,"')");
    PREPARE stmt FROM @sql_command;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE cur1;

    END // 
    DELIMITER ;

Create table replacements with fields string_from and string_to. Add possible auto-changes to this table.

Use procedure call in your code:

    CALL replace_strings('strings','str_text');

heximal · Accepted Answer · 2012-04-17 17:12:38Z

1

why don't you use REPLACE function?

update 
  mytable 
set 
  str_text=replace(str_text, 'Bank of America', 'BOA');

So you should iterate through your array and perform that sql request:

foreach ($myarr as $key=>$val) {
//exec sql
}

answered Apr 17, 2012 at 17:12

heximal

10.5k5 gold badges49 silver badges73 bronze badges

2 Comments

Marki555 Over a year ago

He can also nest the replace() into replace() to have all 100 in one SQL statement, although I don't know if nesting the function 100x won't be slower than running the query each time with different pair.

clst Mar 3 at 17:41

Alternatively a subselect inside a CASE might do the trick: CASE (SELECT col) WHEN 'x' THEN 'other text' WHEN 'y' THEN 'more text' END as my_new_col_name

Collectives™ on Stack Overflow

Is it possible to search and replace an array of strings in MySQL?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related