Replace NonASCII Characters in MYSQL

Question

To convert NON ASCII Characters to ASCII I used the below query

UPDATE tablename
SET columnToCheck = CONVERT(columnToCheck USING ASCII)
 WHERE columnToCheck <> CONVERT(columnToCheck USING ASCII)

It replaces the NON ASCII characters into replacement characters. But is it possible to replace those Non Ascii characters to SPACES.

I tried some options, but its not working. Any suggestion.

I came here looking for the query you posted in your initial question. Thanks! — Steven
– Steven, Commented Jul 11, 2015 at 16:39

M Khalid Junaid · Accepted Answer · 2013-12-24 21:35:56Z

16

Try this one

UPDATE tablename
SET columnToCheck = REPLACE(CONVERT(columnToCheck USING ascii), '?', '')
WHERE ...

or

update tablename
set columnToCheck = replace(columnToCheck , char(146), '');

Reference

answered Dec 24, 2013 at 21:35

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

villamejia Over a year ago

If the database is encoded UTF8 (mysql default), the first code will delete all characters with accents, which could be undesirable.

Abra · Accepted Answer · 2024-04-03 07:02:24Z

0

I was going to add a comment to M Khalid's answer but the site wont let me but here is a small change to it that prevents removing of ?

UPDATE tablename
SET columnToCheck = REPLACE(CONVERT(columnToCheck USING ascii), '??', '')
WHERE ...

edited Apr 3, 2024 at 7:02

Abra

21k14 gold badges101 silver badges74 bronze badges

answered Apr 3, 2024 at 0:14

rob ben

1

2 Comments

Yunnosch Over a year ago

Welcome to StackOverflow. Please, edit and try for How to Answer, describe the effect of what you propose and explain why it helps to solve the problem. Also, you are aware of the need for the commenting privilege, which you do not have. You know the rule ( meta.stackexchange.com/questions/214173/…). In that situation, please do not decide to misuse a different mechanism, an answering post, for what it is not meant for and which you are not yet allowed to do.

Yunnosch Over a year ago

Find help with formatting your post here: stackoverflow.com/help/formatting . Consider taking the tour.

Collectives™ on Stack Overflow

Replace NonASCII Characters in MYSQL

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related