如何使用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屋！