Page 1 of 1

Select where non-english characters

Posted: Tue Mar 13, 2007 2:47 pm
by GeXus
I posted this under php, but should have posted it here..

Is there a way in mysql to select rows that contain non-English characters such as Ã, ¢, etc.

Thanks!

Posted: Tue Mar 13, 2007 3:33 pm
by volka
Are you looking for a WHERE clause that matches all rows containing any non-english characters?

Posted: Tue Mar 13, 2007 4:11 pm
by Weirdan

Code: Select all

mysql> select * from q;
+----+-----------+
| id | t         |
+----+-----------+
|  1 | что-то    |
|  2 | something |
+----+-----------+
2 rows in set (0.00 sec)

mysql> select * from q where t regexp '[^A-Za-z]';
+----+--------+
| id | t      |
+----+--------+
|  1 | что-то |
+----+--------+
1 row in set (0.00 sec)

Posted: Tue Mar 13, 2007 5:10 pm
by GeXus
Weirdan,

That won't work because I have some periods, ampersands, etc. that would get selected by that.

Posted: Tue Mar 13, 2007 5:29 pm
by Weirdan
GeXus wrote: That won't work because I have some periods, ampersands, etc. that would get selected by that.
Well, here's another way:

Code: Select all

mysql> select * from q;
+----+-----------+
| id | t         |
+----+-----------+
|  1 | что-то    |
|  2 | something |
+----+-----------+
2 rows in set (0.00 sec)

mysql> select * from q where cast(t as binary) != cast(convert(t using ascii) as
 binary);
+----+--------+
| id | t      |
+----+--------+
|  1 | что-то |
+----+--------+
1 row in set (0.00 sec)

Posted: Tue Mar 13, 2007 5:31 pm
by GeXus
Bingo! Thank you!
Weirdan wrote:
GeXus wrote: That won't work because I have some periods, ampersands, etc. that would get selected by that.
Well, here's another way:

Code: Select all

mysql> select * from q;
+----+-----------+
| id | t         |
+----+-----------+
|  1 | что-то    |
|  2 | something |
+----+-----------+
2 rows in set (0.00 sec)

mysql> select * from q where cast(t as binary) != cast(convert(t using ascii) as
 binary);
+----+--------+
| id | t      |
+----+--------+
|  1 | что-то |
+----+--------+
1 row in set (0.00 sec)