如何将数字转换为单词-ORACLE [英] How to convert number to words - ORACLE
本文介绍了如何将数字转换为单词-ORACLE的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我写了一个非常简单的查询,结果为500,我需要像下面这样转换该值:-
I have written a very simple query which result a value 500, i need to convert this value like following:-
old value = 500
new value = FIVE HUNDERED/=
推荐答案
使用卢克;)
SqlFiddleDemo
SELECT UPPER(TO_CHAR(TO_DATE(500,'J'),'Jsp')) || '/=' AS new_value
FROM 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,
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的有限支持:
EDIT 2:
Adding limited support for 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
这篇关于如何将数字转换为单词-ORACLE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文