I would like to make a mysql query to catch : أرأء
this char أ may be typed like: ( أ or إ or ا or آ )
so when type:
$SQL=" select * from work where title REGEX '[\\u0622|\\u0623|\\u0625|\\u0627][\\u0631][\\u0622|\\u0623|\\u0625|\\u0627][\\u0621]"
it doesn't work, I think the syntax is not good
分享 MySQL does not have \u escapes. Try to include the raw Unicode character in the query string, and pass it to MySQL in a utf8 connection. How you might do that depends on what language and connector you are using to talk to MySQL. Best would be to pass the pattern string in a parameter from your language's native Unicode string type if you have one; for example in Python-MySQLdb I can just do:
group= u'[أإاآ]'
pattern= u'%sر%sء' % (chars, chars)
connection.execute('SELECT * FROM work WHERE title REGEX %s', [pattern])
(nb no pipe characters needed in a regex character group)
If you really can't get Unicode down your connection at all, MySQL does have a non-standard binary string escape which you could use to get the characters in through another encoding:
WHERE title REGEX 0x5bd8a3d8a5d8a7d8a25dd8b15bd8a3d8a5d8a7d8a25dd8a1 AS utf8 - hex-encoded UTF-8 encoded string
Generally you want to avoid using REGEX because it means any index on the title column will be ineffective and a full table search will be forced.
One alternative would be to do a WHERE title IN a list of all 16 possible strings that would match the expression.
(The most performant approach would be to use a database collation which already treats all four characters as equal. I'm not aware of a collation that matches that sloppily though.)
分享
