Skip to Content

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:

/wp-content/uploads/2012/10/check_147865.png

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-

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Mark Libner

    Hi Paul,

    I agree this can be very handy to have. We primarily use MS and ASE in my shop so I modified your code to work on those DBMSs.

    Thanks,

    Mark L

    IF EXISTS ( select 1 from sysobjects where name = ‘uf_verbalize_group’ )

        DROP FUNCTION uf_verbalize_group

    go

    CREATE FUNCTION dbo.uf_verbalize_group(@sGroup varchar(3)) 

    RETURNS varchar(60) 

    AS

    BEGIN 

        DECLARE @iGroup  integer

        DECLARE @iDigits integer

        DECLARE @iUnits integer

        DECLARE @sWords varchar(60)

        select @iGroup = CAST( @sGroup as integer)

        select @sWords = ”

     

        IF @iGroup > 99

        begin

           select @sWords =   case CAST(Left(@sGroup, 1) as Integer)

                                     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’ when 10 then ‘TEN’ when 11 then ‘ELEVEN’ when 12 then ‘TWELVE’ when 13 then ‘THIRTEEN’ when 14 then ‘FOURTEEN’ when 15 then ‘FIFTEEN’ when 16 then ‘SIXTEEN’ when 17 then ‘SEVENTEEN’ when 18 then ‘EIGHTEEN’ when 19 then ‘NINTEEN’ end + ‘ HUNDRED ‘

        end

     

        select @iDigits = CAST( Right(@sGroup, 2) as Integer)

     

        if @iDigits = 0

        begin

          RETURN @sWords

       end

        else if @iDigits > 0 and @iDigits < 20

        begin

           select @sWords =  @sWords + case @iDigits

                                     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’ when 10 then ‘TEN’ when 11 then ‘ELEVEN’ when 12 then ‘TWELVE’ when 13 then ‘THIRTEEN’ when 14 then ‘FOURTEEN’ when 15 then ‘FIFTEEN’ when 16 then ‘SIXTEEN’ when 17 then ‘SEVENTEEN’ when 18 then ‘EIGHTEEN’ when 19 then ‘NINTEEN’ end + ‘ ‘

        end 

       ELSE  

       begin

          select @sWords =  @sWords + case CAST( left( right( @sGroup, 2), 1) as Integer) when 1 then ‘TEN’ 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

     

          select @iUnits = CAST(Right(@sGroup, 1) as Integer)

     

          IF @iUnits > 0

          begin

           select @sWords =  @sWords + ‘-‘ + case @iUnits

                                     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’ when 10 then ‘TEN’ when 11 then ‘ELEVEN’ when 12 then ‘TWELVE’ when 13 then ‘THIRTEEN’ when 14 then ‘FOURTEEN’ when 15 then ‘FIFTEEN’ when 16 then ‘SIXTEEN’ when 17 then ‘SEVENTEEN’ when 18 then ‘EIGHTEEN’ when 19 then ‘NINTEEN’ end

                                     + ‘ ‘

          end

          ELSE  

             select @sWords = @sWords + ‘ ‘

       end

      

        RETURN @sWords

    END 

    go

    IF EXISTS ( select 1 from sysobjects where name = ‘uf_verbalize_amount’ )

        DROP FUNCTION uf_verbalize_amount

    go

    CREATE FUNCTION dbo.uf_verbalize_amount(@aDecAmount numeric(11,2)) 

    RETURNS VARCHAR(255) 

    AS

    BEGIN 

        DECLARE @sWords   varchar(255)

        DECLARE @iDollars integer

        DECLARE @iCents   integer

        DECLARE @sDollars varchar(255)

        DECLARE @sGroup   varchar(3)

     

        select @aDecAmount = ROUND(@aDecAmount,2)

        –select @iDollars   = round(@aDecAmount,0,1) This works in SQL Server

        select @iDollars = convert(integer,left(convert(varchar,@aDecAmount), len(convert(varchar,@aDecAmount)) – 3))

        select @iCents     = CAST(((@aDecAmount – @iDollars) * 100) as INTEGER)

        select @sWords = ”

         

        IF @iDollars >= 1000000000

        begin

            select @sWords = CAST(@iDollars as varchar(60)) + ‘ DOLLARS AND ‘ + CAST(@iCents as char(2)) + ‘ CENTS’

            RETURN @sWords

        end

     

        /* Make the dollars string 9 digits long */

        select @sDollars = right( replicate(‘0’,9) + CAST(@iDollars as varchar(12)), 9 )

     

        IF @iDollars > 999999

        begin

           /* Verbalize the millions */

           select @sGroup = LEFT(@sDollars, 3)

           IF CAST( @sGroup as integer) > 0

               select @sWords = dbo.uf_verbalize_group(@sGroup) + ‘ MILLION ‘

        end

     

        IF @iDollars > 999

        begin

           /* Verbalize the thousands */

           select @sGroup = substring(@sDollars, 4, 3)

           IF CAST (@sGroup as Integer) > 0

              select @sWords = @sWords + dbo.uf_verbalize_group(@sGroup) + ‘ THOUSAND ‘

        end

       

        IF @iDollars > 0

        begin

           select @sGroup = RIGHT(@sDollars, 3)

           select @sWords = @sWords + dbo.uf_verbalize_group(@sGroup)

        end

        ELSE  

        begin

           select @sWords = ‘ZERO ‘  

           end

          

        IF @iCents > 0

          select @sWords = @sWords + ‘AND ‘ + CAST(@iCents as char(2)) + ‘/100 DOLLARS’

        ELSE  

           select @sWords = @sWords + ‘DOLLARS’

     

        RETURN @sWords

    END 

    go

    (0) 

Leave a Reply