如何将数字转换为单词-ORACLE

用力卢克;）

SqlFiddleDemo

SELECt UPPER(TO_CHAr(TO_DATE(500,'J'),'Jsp')) || '/=' AS new_valueFROM dual;

提示采用

Date

编辑：

添加对以下内容的支持

negative numbers

SqlFiddleDemo

WITH cte AS(  SELECt 10 AS num      FROM dual  UNIOn ALL SELECt -500 FROM dual  UNIOn ALL SELECt 0    FROM dual)SELECt num AS old_value,       depre( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL ) ||       depre( sign( abs(num) ), +1, to_char( to_date( abs(num),'J'),'JSP') ) || '/=' AS new_valueFROM cte

编辑2：

添加对float的有限支持：

SqlFiddleDemo3

WITH cte AS(  SELECt 10 AS num       FROM dual  UNIOn ALL SELECt -500  FROM dual  UNIOn ALL SELECt 0     FROM dual  UNIOn ALL SELECt 10.3  FROM dual  UNIOn ALL SELECt -10.7 FROM dual)SELECt   num AS old_value,  depre( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL )  || depre( sign( abs(num) ), +1, to_char( to_date( abs(TRUNC(num)),'J'),'JSP') )  ||  CASE     WHEN INSTR (num, '.') > 0     THEN  ' POINT ' || TO_CHAr (TO_DATE (TO_NUMBER (SUBSTr(num, INSTR (num, '.') + 1)),'J'),'JSP')     ELSE NULL  END AS new_valueFROM cte

编辑3：

10.3的输出为十点三，但10.3的输出为十点三，10.03的输出为十点三。我怎样才能做到这一点？

根据您要标识的位数，可以为

RPADed

WITH cte AS(  SELECt 10.03 AS num FROM dual  UNIOn ALL  SELECt 10.30 FROM dual  UNIOn ALL  SELECt 10.33 FROM dual)SELECt   num AS old_value,  depre( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL )  || depre( sign( abs(num) ), +1, to_char( to_date( abs(TRUNC(num)),'J'),'JSP') )  ||  CASE     WHEN INSTR (num, '.') > 0     THEN  ' POINT ' || TO_CHAr (TO_DATE (TO_NUMBER (RPAD(SUBSTr(num, INSTR (num, '.') + 1)         ,2,'0')         ),'J'),'JSP')     ELSE NULL  END AS new_valueFROM cte;

db <>fiddle演示

输出：

+-------------+------------------------+|  OLD_VALUE  |       NEW_VALUE        |+-------------+------------------------+|      10.03  | TEN POINT THREE        ||       10.3  | TEN POINT THIRTY       ||      10.33  | TEN POINT THIRTY-THREE |+-------------+------------------------+