如何使用db2 sql查询将数字转换为单词 [英] how to convert number into words using db2 sql query
问题描述
有什么方法可以像Oracle to_date函数一样将日期转换为儒略数字并拼写数字。请帮助在DB2
中做到这一点,例如:-从DUAL中选择to_char(to_date(009341,'J'),'JSP') WORDS;
is there any way to convert a date into Julian numbers and spell the number as is done in Oracle to_date function. Please help how to do that in DB2 FOR EXAMPLE ::-- select to_char(to_date(009341,'J'), 'JSP') "WORDS" from DUAL;
推荐答案
我不知道。您可能需要自己动手。例如。这将达到100。我将它留给读者练习,以创建第二个UDF,然后该UDF可以达到数十亿。。
Not that I am aware of. You might need to roll your own. E.g. this will count to 100. I'll leave it as an exercise for the reader to create a second UDF that can then count up to the billions..
CREATE OR REPLACE FUNCTION HUNDRED(i int)
RETURNS VARCHAR(40) RETURN
CASE WHEN I BETWEEN 1 AND 19 THEN
decode(i,0,'',1,'One',2,'Two',3,'Three',4,'Four',5,'Five',6,'Six',7,'Seven',8,'Eight',9,'Nine'
, 10,'Ten',11,'Eleven',12,'Twelve',13,'Thirteen',14,'Fourteen',15,'Fifteen'
, 16,'Sixteen',17,'Seventeen',18,'Eighteen',19,'Nineteen')
WHEN MOD(i,10) = 0 THEN
decode(i, 20,'Twenty',30,'Thirty',40,'Forty',50,'Fifty',60,'Sixty',70,'Seventy',80,'Eighty',90,'Ninety',100,'Hundred')
ELSE
decode(int(i/10),2,'Twenty',3,'Thirty',4,'Forty',5,'Fifty',6,'Sixty',7,'Seventy',8,'Eighty',9,'Ninety')
|| '-' || decode(mod(i,10),0,'',1,'One',2,'Two',3,'Three',4,'Four',5,'Five',6,'Six',7,'Seven',8,'Eight',9,'Nine')
END
例如第二个这样的UDF将使您最多九十和九十九
..只需扩展逻辑以适应成千上万个等等
E.g. a second UDF such as this will get you up to Nine Hundred and Ninety-Nine
.. just extend the logic to cater for thousands etc
CREATE OR REPLACE FUNCTION INT_TO_WORDS(i int)
RETURNS VARCHAR(80)
RETURN
case when i > 99 then HUNDRED(i/100) || ' Hundred ' || case when mod(i,100) > 0 then 'and ' else '' end else '' END
|| HUNDRED(mod(i,100))
这篇关于如何使用db2 sql查询将数字转换为单词的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!