MySQL string replace

Question

I have a column containing urls  id  url    http   www example com articles updates 43 http   www example com articles updates 866 http   www example com articles updates 323 http   www example com articles updates seo-url http   www example com articles updates 4 something test   I d like to change the word  updates  to  news   Is it possible to do this with a script

User · Answer

The replace function should work for you.

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

User · Answer

You can simply use replace   function      with where clause-   update tabelName set columnName REPLACE columnName  from   to   where condition       without where clause-   update tabelName set columnName REPLACE columnName  from   to      Note  The above query if for update records directly in table  if you want on select query and the data should not be affected in table then can use the following query-  select REPLACE columnName  from   to   as updateRecord

User · Answer

In addition to gmaggio s answer if you need to dynamically REPLACE and UPDATE according to another column you can do for example   UPDATE your table t1 INNER JOIN other table t2 ON t1 field id   t2 field id SET t1 your field   IF LOCATE  articles updates    t1 your field   gt  0   REPLACE t1 your field   articles updates    t2 new folder   t1 your field   WHERE      In my example the string articles news  is stored in other table t2 and there is no need to use LIKE in the WHERE clause

User · Answer

Yes  MySQL has a REPLACE   function   mysql gt  SELECT REPLACE  www mysql com    w    Ww        - gt   WwWwWw mysql com    http   dev mysql com doc refman 5 0 en string-functions html function replace  Note that it s easier if you make that an alias when using SELECT   SELECT REPLACE string column   search    replace   as url

User · Answer

UPDATE your table SET your field   REPLACE your field   articles updates     articles news    WHERE your field LIKE   articles updates      Now rows that were like   http   www example com articles updates 43   will be   http   www example com articles news 43  http   www electrictoolbox com mysql-find-replace-text

[mysql] MySQL string replace

Examples related to mysql

Examples related to replace