Replacing Substrings using SQL, not PHP

Let’s say you have a problem: A recent DB migration has escaped a lot of quote marks within your fields and now your app is acting funny. Your task is to fix the problem, and do it FAST.

Enter the replace() function in MySQL (assumed other RDBMs as well, such as Postgres and MSSQL)
UPDATE table_name SET table_field = replace(table_field, ‘replace_that’, ‘with_this’);

I was seeing a lot of ” text in some forums output, so I was on a mission to make nice with the forums administrator:
update posts_text set post_text = replace(post_text,'\"', ‘'"'’);
Double backslash? Yes. Quotes are OK, but the backslash is an escape character, so to actually print the backslash, you need to escape the escape character. You’d do the same if you wanted n to not be treated as a newline return: \n




No Comments


You can leave the first : )



Leave a Reply