Hang on...
Home

mysql update replace

Share |
Wed, 29/04/2009 - 21:28 — egyember
You sometimes run into the problem that you want to replace a substring occurring in a column with a different string, without touching the rest of the string. The solution is surprisingly simple, thanks to MySQL
UPDATE table SET field=(REPLACE (field, 'search text', 'replace text'));
using the string function REPLACE, all items in the field column with 'search text' get this substring replaced by 'replace text'. Ideal when writing a script is just too much effort. quoted (and modified) from: dev.mysql.com

Comments

Tue, 01/02/2011 - 13:36 — steadweb (not verified)

I recently wanted to replace

I recently wanted to replace a string within MySQL on the fly, but the field could contain 2 items. So I wrapped a REPLACE() within a REPLACE(), such as:

REPLACE(REPLACE(field_name, “what we are looking for”, “replace first instance”), “something else we are looking for”, “replace second instance”)

This is the syntax I used to detect a boolean value:

REPLACE(REPLACE(field, 1, “Yes”), 0, “No”)

Hope this helps!

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr> <br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <caption> <tbody> <tr> <td> <em> <b> <u> <i> <strong> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <embed> <object> <param> <strike>
  • Web page addresses and e-mail addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <c>, <cpp>, <drupal5>, <drupal6>, <java>, <javascript>, <php>, <python>, <ruby>. The supported tag styles are: <foo>, [foo].

More information about formatting options