Convert String into digit in SQL

Question

How to convert any string into digit in SQL without CASE and Decode function. eg. THREE to 3 FOUR to 4 FIVE to 5 SIX to 6 Range is not decided.. can be vary upto N.

Why do you want to do this without `CASE`? What database are you using? What have you tried so far? — Hart CO, Aug 29 '13 at 15:35
Why the limitation on not using `CASE`? Are we bothered about double digits or are we just decoding each one separately i.e. what would you want **12** to display? — gvee, Aug 29 '13 at 15:36
http://stackoverflow.com/questions/4189195/convert-string-ten-to-integer-10 — UltraCommit, Aug 29 '13 at 15:43

score 1 · Answer 1 · answered Aug 29 '13 at 15:54

Well, I'm not sure whether this is what you need, but what about defining a table, say digits, like this:

digit: text | value: int
------------+-----------
one         | 1
two         | 2
three       | 3
           etc.

Then use a query, for example, like this one:

SELECT value FROM digits WHERE digit = 'FIVE'

Sure, it's pretty weird (to say the least), but nonetheless the use of CASE is avoided.

Convert String into digit in SQL

1 Answers1