CODEWITHSUNDEEP
sqloracle-databasedecimalcpu-word
user3404204
user3404204

Reputation: 23

SQL convert amount into word for each digit (including dollar, decimal, and comma)

I am attempting to convert a large dollar amount into words via oracle SQL for each individual digit. For example: $555,555,555.55 = "dollar five five five comma five five five comma five five five period five five".

I tried the Julian Date version of converting numbers to words but it only goes up to the ten-thousand place, no larger.

Please help.

Upvotes: 0

Views: 1113

Answers (2)

user3404204
user3404204

Reputation: 23

Results Image Okay I may have my syntax wrong but it forced an odd column in my results here is my code: 

    SELECT SUM (amount) AS amount,
   REPLACE (
      REPLACE (
         REPLACE (
            REPLACE (
               REPLACE (
                  REPLACE (
                     REPLACE (
                        REPLACE (
                           REPLACE (
                              REPLACE (
                                 REPLACE (
                                    REPLACE (SUM (amount),
                                             '$',
                                             'dollar '),
                                    ',',
                                    'comma '),
                                 '.',
                                 'period '),
                              '0',
                              'zero '),
                           '1',
                           'one '),
                        '2',
                        'two '),
                     '3',
                     'three '),
                  '4',
                  'four '),
               '5',
               'five '),
            '6',
            'six '),
         '7',
         'seven '),
      '8',
      'eight '),
   '9',
   'nine '

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can use nested replace:

select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(col, '$', 'dollar '
                                                                                                      ), ',', 'comma '
                                                                                              ), '0', 'zero '
. . .

Upvotes: 1

Related Questions