Converting Number to Words Using SQL - Once Again

Hi

This post is related to
http://blog.badvised.com/2009/12/converting-number-to-words-using-sql.html

For some reason, one can not post a comment that is too long, so I have opened this post in order to share with you an alternative solution that was sent to me by Sriram Lakshminarasimhan. His solution does not require additional tables as I used in my implementation.
Sriram, thank you for sharing!

WITH CTETAB(WORKSTR,STR,COUNT) AS                             
  (SELECT CHAR('000000000001234567'),CHAR('XXX'),0            
     FROM SYSIBM.SYSDUMMY1                                    
    UNION ALL                                                 
     SELECT WORKSTR,SUBSTR(WORKSTR,1+(3*COUNT),3),COUNT+1     
       FROM CTETAB WHERE COUNT <  6                           
   )                                                          
    SELECT                                                    
    CASE SUBSTR(STR,1,1)                                      
        WHEN '0'  THEN '     '                                
        WHEN '1' THEN 'ONE   HUNDRED AND '                    
        WHEN '2' THEN 'TWO   HUNDRED AND '                    
        WHEN '3' THEN 'THREE HUNDRED AND '                    
        WHEN '4' THEN 'FOUR  HUNDRED AND '                    
        WHEN '5' THEN 'FIVE  HUNDRED AND '                    
        WHEN '6' THEN 'SIX   HUNDRED AND '                    
        WHEN '7' THEN 'SEVEN HUNDRED AND '                    
        WHEN '8' THEN 'EIGHT HUNDRED AND '                    
        WHEN '9' THEN 'NINE  HUNDRED AND '                    
    END     ,                                                 
    CASE SUBSTR(STR,2,1)                                      
        WHEN '0'  THEN                                        
              CASE SUBSTR(STR,3,1)                            
                      WHEN '0' THEN ' '                             
                      WHEN '1' THEN ' ONE '                         
                      WHEN '2' THEN ' TWO '                         
                      WHEN '3' THEN ' THREE '                       
                      WHEN '4' THEN ' FOUR '                        
                      WHEN '5' THEN ' FIVE '                        
                      WHEN '6' THEN ' SIX '                         
                      WHEN '7' THEN ' SEVEN '                       
                      WHEN '8' THEN ' EIGHT '                       
                      WHEN '9' THEN ' NINE  '                       
                    END                                             
              WHEN '1' THEN                                         
                    CASE SUBSTR(STR,3,1)                            
                      WHEN '0' THEN ' TEN '                         
                      WHEN '1' THEN ' ELEVEN '                      
                      WHEN '2' THEN ' TWELVE '                      
                      WHEN '3' THEN ' THIRTEEN '                    
                      WHEN '4' THEN ' FOURTEEN '                    
                      WHEN '5' THEN ' FIFTEEN '                     
                      WHEN '6' THEN ' SIXTEEN '                     
                      WHEN '7' THEN ' SEVENTEEN '                   
                      WHEN '8' THEN ' EIGHTEEN '                    
                      WHEN '9' THEN ' NINETEEN '                    
                    END                                             
              WHEN '2' THEN  ' TWENTY '                             
              WHEN '3' THEN  ' THIRTY '                             
              WHEN '4' THEN  ' FORTY '                            
              WHEN '5' THEN  ' FIFTY '                            
              WHEN '6' THEN  ' SIXTY '                            
              WHEN '7' THEN  ' SEVENTY '                          
              WHEN '8' THEN  ' EIGHTY '                           
              WHEN '9' THEN  ' NINETY '                           
          END ,                                                   
          CASE SUBSTR(STR,2,1)                                    
            WHEN '0' THEN '     '                                 
            WHEN '1' THEN '     '                                 
            ELSE                                                  
             CASE SUBSTR(STR,3,1)                                 
                 WHEN '0' THEN ' '                                
                 WHEN '1' THEN ' ONE '                            
                 WHEN '2' THEN ' TWO '                            
                 WHEN '3' THEN ' THREE '                          
                 WHEN '4' THEN ' FOUR '                           
                 WHEN '5' THEN ' FIVE '                           
                 WHEN '6' THEN ' SIX '                            
                 WHEN '7' THEN ' SEVEN '                          
                 WHEN '8' THEN ' EIGHT '                          
                 WHEN '9' THEN ' NINE  '                          
             END                                                  
          END ,                                                   
          CASE COUNT                                              
            WHEN  1  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'ZILLION ,    '                             
                END                                                
             WHEN  2  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'TRILLION ,   '                             
                END                                                
             WHEN  3  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'BILLION ,   '                              
                END                                                
             WHEN  4  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'MILLION ,   '                              
                END                                                
             WHEN  5  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'THOUSAND ,   '                             
                END                                                
             ELSE                                                  
                '            '                                     
           END                                                     
     FROM CTETAB WHERE COUNT > 0;