网络营销电子商务研究中心 - Mysql search and replace some text in a field

网络营销电子商务研究中心 (https://www.0058.net/index.php)

- MySQL (https://www.0058.net/forumdisplay.php?f=76)

- - Mysql search and replace some text in a field (https://www.0058.net/showthread.php?t=5380)

Mysql search and replace some text in a field

What Mysql query will do a text search and replace in one particular field in a table?

ie search for 'children eyeglasses' and replace with 'kids glasses' so a record with a field with the value : 'How to buy children eyeglasses online?' becomes: 'How to buy kids glasses online?'.

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

Code:

UPDATE table_name SET field = REPLACE(field, 'children eyeglasses', 'kids glasses') WHERE INSTR(field, 'children eyeglasses') > 0;

INSTR (ori_str, sub_str):
ori_str The string to be searched.
sub_str The string to be searched for within the ori_str

MySQL INSTR() takes a string and a substring of it as arguments, and returns an integer which indicates the position of the first occurrence of the substring within the string.