How to find and replace whole words in a MySQL text field?

Question

I have a sentence:

"How to find and replace word in text from mysql database?"

And MySQL table words, with to 3 column id, word and replaceWord. I have more than 4000 words in databese.

Table:

id     word     replaceWord
 1     text     sentence
 2     word     letter
 3     mysql    MySQL
 4     ..       ...
 5     ..       ...
 6     ..       ...

Result:

"How to find and replace letter in sentence from MySQL database?"

I know how to do this without database, but i need database.

 <?php
$text="How to find and replace word in text from mysql database?";
$replaceWord=array(  "text" => "sentence", "word" => "letter", "mysql" => "MySQL");
echo strtr($tekst, $replaceWord);
?>

Answer 1

MySQL 8+

Can use the REGEXP_REPLACE function:

SELECT REGEXP_REPLACE(the_text,
                      CONCAT('(^|\\W)', text_to_find, '(\\W|$)'),
                      CONCAT('$1', replacement_text, '$2')) AS replaced_text
FROM the_table;

See this dbfiddle demo.

Explanation: \W is a regular expression syntax for a non-word character (the backslash needs to be escaped hence \\W). Alternatively, ^ caters for the very start of the text and $ the very end of the text. $1 and $2 put these characters from the parenthesized capturing groups back into the replacement text.

MySQL versions prior to 8

If you are forced to use an earlier version of MySQL it is quite a lot trickier but still technically possible using a custom written regular expression replacer - see this answer for details.

---

MariaDB 10.0.5+

As pointed out by Paul Spiegel in the comments, the only difference in MariaDB versions supporting REGEXP_REPLACE is the use of \\1 and \\2 instead of $1 and $2 to identify the capturing groups:

SELECT REGEXP_REPLACE(the_text,
                      CONCAT('(^|\\W)', text_to_find, '(\\W|$)'),
                      CONCAT('\\1', replacement_text, '\\2')) AS replaced_text
FROM the_table;

See this dbfiddle demo.

Answer 2

update YourTable, Words
    set YourTable.YourColumn = replace(YourTable.YourColumn, Words.word, Words.replaceWord)

Answer 3

select REPLACE(fieldOrString, SearchString, ReplaceString) from table

Answer 4

Here's a terrible idea in terms of scalability: you could just iterate over the sentence word by word, and on each one do a query for where the word exists in 'word'. If it does exist (returns data) then do a replace with the content from 'replaceWord'

EDIT: Not totally database based, but more so than your current version.

Answer 5

//load all replacements
$result = mysql_query("SELECT * FROM YourTableNameHere");
//replace all words
$words = array();
$replacewords =array();
while ($row = mysql_fetch_assoc($result)) {
    $words[] = $row['word'];
    $replacewords[] = $row['replaceword'];
}
$text = str_replace($words,$replacewords);

If you need preg_replace as well: You must add the column isPattern to your table, then you can do this:

//load all replacements
$result = mysql_query("SELECT * FROM YourTableNameHere");
//replace all words
$words = array();
$replacewords = array();
$preg_words = array();
$preg_replacewords = array();
while ($row = mysql_fetch_assoc($result)) {
    if(!$row['isPattern']){        
        $words[] = $row['word'];
        $replacewords[] = $row['replaceword'];
    }
    else{
        $preg_words[] = $row['word'];
        $preg_replacewords[] = $row['replaceword'];
    }
}
$text = str_replace($words,$replacewords);
$text = $preg_replace($preg_words,$preg_replacewords);

Answer 6

After I started to implement my own query based on the answer by @SteveChambers I found the substitution happened only once per result row. For example, given the table records mentioned by the OP, this query:

SELECT 
    word,
    replaceWord,
    REGEXP_REPLACE(
        'How to find and replace word in text from mysql database?', 
        CONCAT('(?i)(^|\\W)', word, '(\\W|$)'),
        CONCAT('\\1', replaceWord, '\\2')
    ) AS replaced_text 
FROM 
    words 
WHERE 
    'How to find and replace word in text from mysql database?' REGEXP CONCAT('(?i)(^|\\W)', word, '(\\W|$)');

would return distinct 3 rows of results:

word  | replaceWord | replaced_text
------+-------------+--------------
text  | sentence    | How to find and replace letter in text from mysql database?
word  | letter      | How to find and replace word in sentence from mysql database?
mysql | MySQL       | How to find and replace word in text from MySQL database?

Notice each row has only one word replaced.

After some discussion, we got to the conclusion that recursion was required. I managed to achieve that without a procedure or function with the following query:

SELECT 
    (@i := @i + 1) AS i, 
    @tmp := IFNULL(
        REGEXP_REPLACE(
            @tmp, 
            CONCAT('(?i)(^|\\W)', word, '(\\W|$)'), 
            CONCAT('\\1', replaceWord, '\\2')
        ),
        @tmp
    ) AS replaced_text 
FROM 
    (
        SELECT 
            @tmp := 'How to find and replace word in text from mysql database?',
            @i := 0
    ) x
LEFT JOIN 
    words 
ON 
    @tmp REGEXP CONCAT('(?i)(^|\\W)', word, '(\\W|$)') 
ORDER BY i DESC 
LIMIT 1;

This query recursively replaces each word in the original string, accumulating the replacements. It uses an index (@i) to number the rows. Finally, it returns only the last result (greater index), which contains all accumulate replacements.

It uses a LEFT JOIN combined with IFNULL to return the original string in case no substitution is made.

Answer 7

$tablesarray include column array where you want to search Any word or letter

$PDO database connection

    $tablesarray['tablename1']= array('column1' ,'column2' );
    $tablesarray['tablename2']= array('column1' ,'column2' );
    
    $wordstoreplace = ['searchedwordOrLetter1'=>'replacedwordOrLetter1' , 'searchedwordOrLetter2'=>'replacedwordOrLetter2' ]
    foreach($repairkeyarray as $table => $columns){
        foreach($columns as $column){
            foreach($wordstoreplace as $searched => $replaced ){
                $PDO->query("update $table set `$column` = replace(`$column`, '$searched', '$replaced') WHERE `$column` LIKE '%$searched%';");
                @$PDO->execute();
            }
        }
    }