SQL function to “verbalize” dollar amounts

For anyone that has ever worked on a financial application, the chances are pretty good that you’ve been asked to develop a facsimile of a “check” or other type of disbursement – including the field where you “spell out” the dollar and cents value of the check.  This is a Frequently Asked Question over on the PowerBuilder forums, but the solutions provided are usually PowerScript code in a client-side NVO method.   Now, this is not a poor solution in and of itself, but this approach locks that functionality away in the client-side PowerScript, and doesn’t afford much reuse.

Here’s what I’m talking about:

This posting outlines a server-side approach, and implements this feature as a stored function in a SQL Anywhere 12 database.  I tried to write the code as “generic” as possible, using ANSI-standard built-in functions so that it could be ported to other DBMS platforms.

CREATE or replace FUNCTION uf_verbalize_group( IN sGroup varchar(3) )
RETURNS varchar(60)
DETERMINISTIC
BEGIN
    DECLARE iGroup  integer;
    DECLARE iHundreds integer;
    DECLARE iTens integer;
    DECLARE iDigits integer;
    DECLARE iUnits integer;
    DECLARE sWords varchar(60);
    declare local temporary table units(
        id        integer     not null default autoincrement,
        unit_name varchar(10) not null,
        primary key (id)
       ) not transactional;
    declare local temporary table tens(
        id integer not null default autoincrement,
        tens_name varchar(10) not null,
        primary key (id)
       ) not transactional;
    insert into units (unit_name )
          select name from OpenString( value string(
              'ONE|TWO|THREE|FOUR|FIVE|SIX|SEVEN|EIGHT|NINE|TEN|',
              'ELEVEN|TWELVE|THIRTEEN|FOURTEEN|FIFTEEN|',
              'SIXTEEN|SEVENTEEN|EIGHTEEN|NINETEEN' ) )
              with (name varchar(10))
              option( row delimited by '|') w1;
    insert into tens (tens_name )
          select name from OpenString( value
              'TEN|TWENTY|THIRTY|FORTY|FIFTY|SIXTY|SEVENTY|EIGHTY|NINETY' )
              with (name varchar(10))
              option( row delimited by '|') w10;
  
    SET iGroup = CAST( sGroup as integer);
    IF iGroup > 99 THEN
       SET iHundreds = CAST(Left(sGroup, 1) as Integer);
       SET sWords =
        (SELECT unit_name || ' HUNDRED '
           from UNITS
          where id = iHundreds ) ;
    END IF ;
    set iDigits = CAST( Right(sGroup, 2) as Integer);
    CASE 
     WHEN iDigits = 0 THEN
      RETURN sWords ;
     WHEN iDigits > 0 and iDigits < 20 THEN
      set sWords =
        (SELECT sWords || unit_name || ' '
           FROM units
          WHERE id = iDigits) ;
     ELSE
      SET iTens = CAST( left( right( sGroup, 2), 1) as Integer);
      set sWords =
        (SELECT sWords || tens_name
           from tens
          where id = iTens);
      SET iUnits = CAST(Right(sGroup, 1) as Integer);
      IF iUnits > 0 THEN
         set sWords =
           (SELECT sWords || '-' || unit_name || ' '
              FROM units
             where id = iUnits);
      ELSE
         SET sWords = sWords || ' ' ;
      END IF ;
     END CASE;
    RETURN sWords;
END
go
CREATE or replace FUNCTION uf_verbalize_amount( IN aDecAmount numeric )
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE sWords   varchar(255);
    DECLARE iDollars integer;
    DECLARE iCents   integer;
    DECLARE sDollars varchar(255);
    DECLARE sGroup   varchar(3);
    SET aDecAmount = ROUND( aDecAmount, 2 );
    SET iDollars   = TRUNCNUM( aDecAmount, 0 );
    SET iCents     = CAST(((aDecAmount - iDollars) * 100) as INTEGER);
    IF iDollars >= 1000000000 THEN
       SET sWords = CAST(iDollars as varchar(60)) || ' DOLLARS AND ' || CAST(iCents as char(2)) || ' CENTS' ;
       RETURN sWords ;
    END IF;
    // Make the dollars string 9 digits long
    SET sDollars = right( REPEAT('0',9) || CAST(iDollars as varchar(12)), 9 );
    IF iDollars > 999999 THEN
       // Verbalize the millions
       SET sGroup = LEFT(sDollars, 3);
       IF CAST( sGroup as integer) > 0 THEN
          SET sWords = uf_verbalize_group(sGroup) || ' MILLION ';
       END IF ;
    END IF ;
    IF iDollars > 999 THEN
       // Verbalize the thousands
       SET sGroup = SUBSTR(sDollars, 4, 3);
       IF CAST (sGroup as Integer) > 0 THEN
          SET sWords = sWords || uf_verbalize_group(sGroup) || ' THOUSAND ' ;
       END IF
    END IF ;
    IF iDollars > 0 THEN
       SET sGroup = RIGHT(sDollars, 3);
       SET sWords = sWords || uf_verbalize_group(sGroup);
    ELSE
       SET sWords = 'ZERO '
    END IF ;
    IF iCents > 0 THEN
       SET sWords = sWords || 'AND ' || CAST(iCents as char(2)) || '/100 DOLLARS' ;
    ELSE
       SET sWords = sWords + 'DOLLARS' ;
    END IF ;
    RETURN sWords;
END
go

-Paul Horan-