Replacing a character with the " character in a database field

[rasinc]

I had to do a quick import of information into a MySQL database and double quotes were in the original data. The program I am working with does not handle them properly, so I did a search and replace with another character for the import.

Then I needed to go back into the file at the database level and replace the character after importing.

So basically this text

Long 2" straps

would not import properly. So I just changed it.

I was wondering what the appropriate SQL statement would be to do this at the SQL level. I did resolve it and fixed it up by using MS Access and linking to a table and using the search and replace feature. I just didn't have time to sort it out with SQL. However, writing proper syntax would have been faster.

So, for future reference, I was wondering what the appropriate syntax is to do this? I tried the following (no conditions, I wanted to update any possible record - unless that is my problem - didn't think of it until just now)

Update myTable
Set textfield = Replace (textfield, 'CharacterUsed', '"')

I tried this and it didn't update anything. I am assuming I have to escape the " character but I can't find any examples and don't know how to do this.

TIA, rasinc

[smendoza]

That's the correct syntax, did it give you a error?

[rasinc]

No errors, just no records affected.

[smendoza]

This might sound dumb, but did you use the right special character? I've done things like that before and was scratching my head wondering why its not working.

[szlamany]

If you are looking to remove the " character in this text you posted

Long 2" straps

with a BLANK, for instance...

Then wouldn't it be Replace(textfield,'"',' ')

Or is mysql replace backwards compared to MSSql in the order of replace arguments.

btw - sometimes the " is really a unicode `` or something odd like that - are you sure you are using the right "original character"...

For instance - can you search for the " with a WHERE clause that you type as :

Long 2" straps

Or do you need to copy/paste that data to make the WHERE clause work?

[rasinc]

Basically I had the text

Long 2" straps

and converted it to the following to import it

Long 2$$ straps

and then wanted to change it back to

Long 2" straps

for the client to use it.

I'll test it again tomorrow. I am not in the office where the full data set it right now.

Thanks for your responses. I'll check it again. Maybe I just messed up the syntax but was in too much of a hurry to try to figure it out. So I just tried another method.

[rasinc]

Finally had a chance to get back to this again. Sorry for the delay. I spent some time redoing everything from the export from Excel to the import.

I found that when exporting from Excel as a *.prn file, the extra ""'s do not get inserted when there is a " in the text. This will help me in the future.

Anyway, back to the issue at hand, it turns out that I must have done something wrong with my syntax. I went back and copied my pseudocode from Post #1 and put in my real table, field name and character used and it updated without any problems.

So my apologies to everyone for such a silly mistake but going back and reviewing everything made me learn something as well. Thanks to smendoza and szlamany for helping. The answers were in your posts.