How can I find non-ASCII characters in MySQL?

MySQLMySQLi Database

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)
raja
Updated on 24-Jun-2020 14:10:12

Advertisements