我写了一个非常简单的查询,结果为500,我需要像下面这样转换该值:-
old value = 500 new value = FIVE HUNDERED/=
用力卢克;)
SqlFiddleDemo
SELECT UPPER(TO_CHAR(TO_DATE(500,'J'),'Jsp')) || '/=' AS new_value FROM dual;
提示采用Date拼写形式。
Date
编辑:
添加对以下内容的支持negative numbers:
negative numbers
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, decode( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL ) || decode( sign( abs(num) ), +1, to_char( to_date( abs(num),'J'),'JSP') ) || '/=' AS new_value FROM cte
添加对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, decode( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL ) || decode( 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_value FROM cte
编辑3:
10.3的输出为十点三,但10.3的输出为十点三,10.03的输出为十点三。我怎样才能做到这一点?
根据您要标识的位数,可以为RPADed0:
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, decode( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL ) || decode( 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_value FROM cte;
db <> fiddle演示
输出:
+-------------+------------------------+ | OLD_VALUE | NEW_VALUE | +-------------+------------------------+ | 10.03 | TEN POINT THREE | | 10.3 | TEN POINT THIRTY | | 10.33 | TEN POINT THIRTY-THREE | +-------------+------------------------+