MySQL Find and Replace Characters

Posted on 2009-12-29
Last Modified: 2013-12-12
I need to do a find and replace mysql statement.

I wrote this statement to replace the odd characters with a single quote in table jos_content and row fulltext.

update jos_content set fulltext = replace(fulltext, 'â¬"', '\'')

I get an error. I have tried finding the html entities for each of the three characters and replacing them but still get an error. Anythoughts?

Question by:heavymark
    LVL 107

    Expert Comment

    by:Ray Paseur
    How big is this table?  If it is less than a few million lines and you do not have to do this every few seconds, you might want to consider a PHP + MySQL solution.

    Also, "I get an error." is not a standard message that I recognize.  We can have a better chance of helping you if you show us the inputs, your code, the outputs and tell us about your desired outputs.

    Best regards, ~Ray

    Accepted Solution

    Actually I ran the same update query as listed above in Navicat instead of PhpMyAdmin and it works. It seems Navicat has built in functionality to handle the odd characters.

    I did not provide the exact error message, because it was not relevant to the solution. That its simply a matter of not being able to use those characters mentioned above in the update query. Such as not being able to use a single quote without using an a escape \. So I was simply looking for the way to "escape" or encode those particular characters as I have seen the same question all over the internet.

    But once again, pasting the same query in Navicat's does all the user to run any and all of the odd characters I have come across rather than exporting the database and changing them in a local application and then reuploading.
    LVL 107

    Expert Comment

    by:Ray Paseur
    "I did not provide the exact error message, because it was not relevant to the solution."

    Wow, I wish the developers who write software could know they were wasting their time working on error messages.  Think of the time we could all save if we didn't need the error messages!



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    The viewer will learn how to count occurrences of each item in an array.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now