Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can I find non-ASCII characters in MySQL?
Non ASCII characters are characters such as the pound symbol(£), trademark symbol, plusminus symbol etc. To find the non-ASCII characters from the table, the following steps are required −
First a table is created with the help of the create command which is given as follows −
mysql> CREATE table NonASciiDemo -> ( -> NonAScii varchar(100) -> ); Query OK, 0 rows affected (0.61 sec)
After that the records are inserted into the table with the help of the insert command which is as follows −
mysql> INSERT into NonASciiDemo values('-,-');
Query OK, 1 row affected (0.18 sec)
mysql> INSERT into NonASciiDemo values(' ');
Query OK, 1 row affected (0.23 sec)
mysql> INSERT into NonASciiDemo values('£');
Query OK, 1 row affected (0.30 sec)
mysql> INSERT into NonASciiDemo values('123abcd£');
Query OK, 1 row affected (0.24 sec)Four records are inserted into the table as shown above in which two records contain non ASCII characters and two records contain ASCII characters.
To display allthe records, select command is used as follows −
SELECT * from NonASciiDemo;
The following is the output
+----------+ | NonAScii | +----------+ | -,- | | | | £ | | 123abcd£ | +----------+ 4 rows in set (0.00 sec)
The syntax to find the non ASCII characters is given as follows −
SELECT * FROM yourTableName WHERE NOT HEX(yourColumnName) REGEXP '^([0-7][0- 9A-F])*$';
The query to get the non ASCII characters using the above syntax is given as follows −
mysql> SELECT * FROM NonASciiDemo WHERE NOT HEX(NonAScii) REGEXP '^([0-7][0-9AF])*$';
The following is the output of the above query −
+----------+ | NonAScii | +----------+ | £ | | 123abcd£ | +----------+ 2 rows in set (0.00 sec)
1K+ Views
