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)