convert a string number like "four" into '4' in mysql

Question

In mysql database, I have a column named runs of varchar type in one table. Different rows consists of "FOUR","SIX","BYES",etc in runs column. Now I want to sum this runs column. so these ("FOUR","SIX",etc except "BYES") should convert into (4,6) of int data type.

Is there any function to make assume these string as integers and sum them to give a total. Please anyone help me?

Do you have any limit? E.g. does "TWO THOUSAND THIRTEEN" qualify? — Déjà vu, Jun 05 '13 at 16:15
@ring0 It will still be most efficient to create a lookup table. — Kermit, Jun 05 '13 at 16:16
@MichaelBerkowski no worries. still not really constructive/ambiguous/vague — Kermit, Jun 05 '13 at 16:16
No, the question which i asked is not there, i am asking about ans in mysql. it was asked in php — smnth90, Jun 08 '13 at 10:08

score 4 · Accepted Answer · answered Jun 05 '13 at 16:12

4

No, there is no function. You can create a "lookup" table that has the "word" version of the integer, and its integer value.

word   value
------------
one    1
two    2
three  3
four   4
five   5
...

answered Jun 05 '13 at 16:12

Kermit

33,827
13
85
121

convert a string number like "four" into '4' in mysql

1 Answers1