MySQL Converting string Indian numbers to standard integer

Question

I have prices stored as varchar written in Indian numbers, e.g. '١٨٠٠'. Is there a way to convert these data to standard integers e.g. 1800?

input table

|price|
|١٨٠٠ |
|200  |

output table

|price|
|1800 |
|200  |

I tried FOMAT() with en_US locale but it returns zero

Edit your question and provide sample data and desired results. Also, why are numbers stored as strings and not as, well, numbers? — Gordon Linoff, Sep 25 '20 at 11:49
What have you tried so far, what do you want to achieve? If these are usual strings, it should not be too hard to convert them — Nico Haase, Sep 25 '20 at 11:52
Does this answer your question? [convert Persian/Arabic numbers to English numbers](https://stackoverflow.com/questions/11766726/convert-persian-arabic-numbers-to-english-numbers) — Christian Baumann, Sep 25 '20 at 11:57
NO I need mysql not php, I tried FOMAT() with en_US locale but it returns zero — Diaa Saada, Sep 25 '20 at 14:08

score 3 · Accepted Answer · answered Sep 25 '20 at 12:04

3

If the value is clear indian number, without additional symbols, then use

SELECT UNHEX(REPLACE(HEX(indian_value), @prefix, '3')) AS arabic_value

For the symbols in your sample data @prefix = 'D9A'.

answered Sep 25 '20 at 12:04

Akina

1 Answers1