how to convert number into words using db2 sql que

2019-09-22 02:51发布

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;

回答1:

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

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))