Select where non-english characters

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Select where non-english characters

Post 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!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Are you looking for a WHERE clause that matches all rows containing any non-english characters?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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)
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Weirdan,

That won't work because I have some periods, ampersands, etc. that would get selected by that.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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)
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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)
Post Reply