How can I find non-ASCII characters in MySQL?
Query to find data as “ASCII”,
SELECT * FROM tableName WHERE NOT columnToCheck REGEXP '[A-Za-z0-9]';
|
1 |
SELECT * FROM tableName WHERE NOT columnToCheck REGEXP '[A-Za-z0-9]'; |
Query to find the “Non ASCII ”
SELECT * FROM my_table WHERE NOT HEX(my_column) REGEXP '^([0-7][0-9A-F])*$'
|
1 |
SELECT * FROM my_table WHERE NOT HEX(my_column) REGEXP '^([0-7][0-9A-F])*$' |
How to replace Non ASCII character into table using query
Now in first step create alias column and copy/update data to new column for the backup.
Now in second run the below column to remove Non ASCII characters :).
UPDATE table_name
SET column_alias = REPLACE(CONVERT(column_nume USING ascii), '?', '');
|
1 2 |
UPDATE table_name SET column_alias = REPLACE(CONVERT(column_nume USING ascii), '?', ''); |
Sometimes Noticed that characters like “(” or “)” were in the link. So we made another query to replace special characters in alias column.
UPDATE table_name SET column_alias = REPLACE(column_alias,")","");
|
1 |
UPDATE table_name SET column_alias = REPLACE(column_alias,")",""); |
