In Mysql database i want all non ascii characters to be saved in valid ascii characters. What should be the insert query in this case? Say i have one insert below:
insert into test values (1, ‘any non ascii character string’ )
I’m not clear about what you are wanting to do.
ASCII has 128 valid characters. There is no way to make characters outside of this range into valid ASCII characters.
Sorry not to clarify.
Below is an example of update query. I have to run below query on the first_name column to fix non ascii characters to convert into valid ascii characters. Is there any way i can convert non ascii characters to ascii characters at the time of insert query or through the table attributes? I can put CONVERT(first_name USING ASCII) in insert query but my concern is about table if there is any settings in the table which auto converts non ascii to valid ascii characters.
UPDATE mytable SET first_name = CONVERT(first_name USING ASCII) WHERE first_name <> CONVERT(first_name USING ASCII)
Thanks, I think I understand.
When I started years ago I used ASCII and it was good enough for a while. But eventually it wasn’t and I switched to UTF-8 - I have never considered reverting back to ASCII
I have done some coding in PHP involving translation and for me it was not the easiest learning curve, but it was doable.
Please correct me if I’m wrong.
You have an existing database that has fields containing characters outside of ASCII range, and you want to convert any such characters to their ASCII equivalents.
I think this will be complex if not impossible depending. That is, AFAIK, certain glyphs have no corresponding ASCII equivalents (eg. Chinese) But if they are like
Åâáèöçñß
there might be a way to convert using database functions. Though because I have no experience doing conversion using database functions and some experience using PHP functions, my likely first choice on how to approach the problem would be to use PHP functions.
A lot may depend on
- whether or not this is a one-off task or will be ongoing
- how large the database is
- how many fields would be involved
For me, up to a point, I don’t mind tedious one-off work.
So I would run a COUNT query to find out just how many fields there were that needed changing.
If there were only relatively few, I would be tempted to slog through the changes one at a time.
If there were more than I felt like tackling the hard way, I would write a “map” in PHP to do the character replacement or use PHP translation functions.
Hopefully someone that has had experience using MySQL functions will join in, but I’ll take a look at the MySQL documentation to see what might be possible ASAP
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.