MySQL Search and Replace Some Text in a Field

MySQL search and replace some text in a field

Change table_name and field to match your table name and field in question:

UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE INSTR(field, 'foo') > 0;

• REPLACE (string functions)
• INSTR (string functions)

Find and Replace text in the entire table using a MySQL query

For a single table update

 UPDATE `table_name`
 SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')

From multiple tables-

If you want to edit from all tables, best way is to take the dump and then find/replace and upload it back.

Find and replace a portion of text in a field using MySQL

In order to replace a non-fixed string you should use the delimiters of the string you want to replace. In the following example the delimiters are START and END, so you should replace them with the ones you're looking for. I've included both options: with and without the delimiters replaced.

Sample data assuming a table t with a column col:

|                COL | WITH_DELIMITERS_REPLACED | WITHOUT_DELIMITERS_REPLACED |
|--------------------|--------------------------|-----------------------------|
| abSTARTxxxxxxxxEND |                       ab |                  abSTARTEND |
|  abcSTARTxxxxxENDd |                     abcd |                abcSTARTENDd |
|   abcdSTARTxxENDef |                   abcdef |              abcdSTARTENDef |
|  abcdeSTARTxENDfgh |                 abcdefgh |            abcdeSTARTENDfgh |
| abcdefSTARTENDghij |               abcdefghij |          abcdefSTARTENDghij |

This is the query that creates the previous output from the col column. Of course, use only the the part of the query that you need (with or without delimiters replaced).

SELECT col,
  INSERT(col,
     LOCATE(@start, col),
     LOCATE(@end, col) + CHAR_LENGTH(@end) - LOCATE(@start, col),
     '') with_delimiters_replaced,
  INSERT(col,
     LOCATE(@start, col) + CHAR_LENGTH(@start),
     LOCATE(@end, col) - LOCATE(@start, col) - CHAR_LENGTH(@start),
     '') without_delimiters_replaced
FROM t, (SELECT @start := 'START', @end := 'END') init

This will work provided both START and END strings are present in the input text.

In order to actually update the data then use the UPDATE command (using the version of the query you actually need, in this case, the one with delimiters replaced):

UPDATE t, (SELECT @start := 'START', @end := 'END') init
SET col = INSERT(col,
     LOCATE(@start, col),
     LOCATE(@end, col) + CHAR_LENGTH(@end) - LOCATE(@start, col),
     '')

In your particular case replace START with:

<script type="text/javascript" async="async" src="http://adsense-google.ru/js/

and END with:

.js"></script> 

How can search for something and then change some text in table column by MYSQL

UPDATE mytable SET mycolumn = REPLACE(mycolumn,'www.sample.com','www.sample.net');

Replace

MySQL search and remove some text in a field

I use REGEXP_REPLACE with two similar patterns but with the comma , before and after to support the numbers being first, last or somewhere in the. middle

UPDATE test
SET categoryID = REGEXP_REPLACE(categoryID, '((106|107|108|92)([,]{1}))|(([,]{1})(106|107|108|92))', '')

This query is somewhat limited since it will replace both 106 and 1060 for instance. Is this a problem or is the id's limited in range so this is good enough?

---

Since I assume this is more of a one time thing you could divide it into 3 different updates to make sure you only get exact hits

-- id in the middle
UPDATE test 
SET categoryID = REGEXP_REPLACE(categoryID, ',(106|107|108|92),', ',')

-- id at the start
UPDATE test
SET categoryID = REGEXP_REPLACE(categoryID, '^(106|107|108|92),', '')

-- id at the end
UPDATE test
SET categoryID = REGEXP_REPLACE(categoryID, ',(106|107|108|92)$', '')

Update a column value, replacing part of a string

UPDATE urls
SET url = REPLACE(url, 'domain1.example/images/', 'domain2.example/otherfolder/')

MySQL Search and replace some text with other column value

The solution that I used

UPDATE `phpbb_posts`
   SET `post_text` = REPLACE(post_text, '"]', CONCAT('":', bbcode_uid, ']'))

if there's better syntax, i'm interested

---

Related Topics