Is it possible to compare unicode numbers with mysql int columns?

Question

I have a table with an integer value. In my application there is a text field that user enters his desired number to search. If user enter number in English there is no problem. but if his keyboard is not english and enters a number that exists in table mysql doesn't find it. I can't change application to convert user input to english numbers. Is there a way to solve this problem in mysql(config, table structure,etc.)?

for example I have this record in database:

Order_number = 112, ...

If user search for 112 in english, mysql returns the above row. but if user enters 112 for example in persian(۱۱۲) mysql returns an empty set.

[This post has info about that conversion](http://stackoverflow.com/questions/11766726/convert-arabic-persian-numbers-to-english-numbers) — Mark Wilkins, Dec 18 '12 at 00:20
How many different forms can the numbers take on? If its not too many, and one to one mappings exist to the english style numbers, you might be able to just use a bunch of REPLACE() calls. — goat, Dec 18 '12 at 00:20
Numbers (e.g. INT values) in MySQL has to be addressed with digits 0..9 as designed. All other will be interpreted as chars. IMHO no way without app modification — Sir Rufo, Dec 18 '12 at 00:25

score 1 · Answer 1 · answered Dec 18 '12 at 01:10

Not directly.

mysql> SELECT CAST(112 AS CHAR) = '۱۱۲' COLLATE utf8_unicode_ci;
+------------------------------------------------------+
| CAST(112 AS CHAR) = '۱۱۲' COLLATE utf8_unicode_ci    |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

score 1 · Answer 2 · answered Dec 18 '12 at 01:13

1

Worst case, you could do something like

... where 112 = replace('۱', 1, replace('۲', 2, 'user input here'));

I assume all inputs have 10 distinct digits in their number representations, just that they use different characters to represent the digits.

answered Dec 18 '12 at 01:13

goat

31,486
7
73
96

[I hope you're not serious about this...](http://en.wikipedia.org/wiki/Numerical_digit#Numerals_in_most_popular_systems) – Ignacio Vazquez-Abrams Dec 18 '12 at 01:16

Is it possible to compare unicode numbers with mysql int columns?

2 Answers2