Numbers to Words in SQL

How do I convert numbers into words, like 16 into "sixteen"?  I have seen this question and I have had this question many times as I have created reports and legal documents.  I often run into situations where I need to take a day or number and return, for example, "twenty-one (21)" or "second (2nd) ".  

There are a lot of answers out there on how to do this and some are very long and lengthy, some not very efficient and some pretty ingenious ones.  I have altered these to make a few variations, first being NumToWords and ,second, DaysToWords.  

NumToWords will take a number and return the word equivalent; ie: 16 to Sixteen (16) - case sensitivity is a preference and easily altered.

DaysToWords is similiar however the words are day specific not number word.  Using the same example would result in Sixteenth (16th).  Below is the coding for each one.  Giving credit to the original creator Srinivas Sampath, with my own alterations and tweaks.

Numbers to Words

CREATE FUNCTION [dbo].[udf_NumToWords] (
  @Number Numeric (38, 0) -- Input number with as many as 18 digits
 ) RETURNS VARCHAR(8000) 
 /*
 * Converts a integer number as large as 34 digits into the 
 * equivalent words.  
 *
 * Attribution: Based on NumberToWords by Srinivas Sampath
 *        as revised by Nick Barclay and Mike Christiansen
 *
 * Example:
 if number is 61: RETURNS: "sixty-one (61)"
 If number is 1001: RETURNS: "one thousand one (1,001)
 *
 ****************************************************************/
 AS BEGIN
 DECLARE @inputNumber VARCHAR(38)
 DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
 DECLARE @outputString VARCHAR(8000)
 DECLARE @length INT
 DECLARE @counter INT
 DECLARE @loops INT
 DECLARE @position INT
 DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
 DECLARE @tensones CHAR(2)
 DECLARE @hundreds CHAR(1)
 DECLARE @tens CHAR(1)
 DECLARE @ones CHAR(1)
 IF @Number = 0 Return 'zero (0)'
 -- initialize the variables
 SELECT @inputNumber = CONVERT(varchar(38), @Number)
      , @outputString = ''
      , @counter = 1
 SELECT @length   = LEN(@inputNumber)
      , @position = LEN(@inputNumber) - 2
      , @loops    = LEN(@inputNumber)/3
 -- make sure there is an extra loop added for the remaining numbers
 IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
 -- insert data for the numbers and words
 INSERT INTO @NumbersTable   SELECT '00', ''
     UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
     UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
     UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
     UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
     UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
     UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
     UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
     UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
     UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
     UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
     UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
     UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
     UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
     UNION ALL SELECT '90', 'ninety'   
 WHILE @counter <= @loops BEGIN
  -- get chunks of 3 numbers at a time, padded with leading zeros
  SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
  IF @chunk <> '000' BEGIN
   SELECT @tensones = SUBSTRING(@chunk, 2, 2)
        , @hundreds = SUBSTRING(@chunk, 1, 1)
        , @tens = SUBSTRING(@chunk, 2, 1)
        , @ones = SUBSTRING(@chunk, 3, 1)
   -- If twenty or less, use the word directly from @NumbersTable
   IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
    SET @outputString = (SELECT word 
                                       FROM @NumbersTable 
                                       WHERE @tensones = number)
                    + CASE @counter WHEN 1 THEN '' -- No name
                        WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                        WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                        WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                        WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                        WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                        WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                        ELSE '' END
                                + @outputString
       END
    ELSE BEGIN -- break down the ones and the tens separately
              SET @outputString = ' ' 
                             + (SELECT word 
                                     FROM @NumbersTable 
                                     WHERE @tens + '0' = number)
               + '-'
                              + (SELECT word 
                                     FROM @NumbersTable 
                                     WHERE '0'+ @ones = number)
                    + CASE @counter WHEN 1 THEN '' -- No name
                        WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                        WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                        WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                        WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                        WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                        WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                        ELSE '' END
                             + @outputString
   END
   -- now get the hundreds
   IF @hundreds <> '0' BEGIN
    SET @outputString  = (SELECT word 
                                       FROM @NumbersTable 
                                       WHERE '0' + @hundreds = number)
                  + ' hundred ' 
                                 + @outputString
   END
  END
  SELECT @counter = @counter + 1
       , @position = @position - 3
 END
 -- Remove any double spaces
 SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' '))) + ' (' + convert(varchar,FORMAT(@Number,'#,##')) +')'
 SET @outputstring = LEFT(@outputstring, 1) + SUBSTRING(@outputstring, 2, 8000)
 RETURN @outputString -- return the result
 END

Days to Words

There is little change from NumToWords. For one I have limited the numbers to under 100 as I did not see a need for a large number format in most legal documentation beyond 100; generally used when talking about the day of the month, which will not exceed 31. I am toying with the idea of altering this to qualify entry to verify that it is a valid day - ie using a date entry instead of a number. As you read through it you'll see the differences.

CREATE FUNCTION [dbo].[udf_DaysToWords] (
  @Number Numeric (5, 0) -- Input number up to 99
 ) RETURNS VARCHAR(8000) 
 /*
 * Converts a integer number under 100 into the 
 * equivalent words and number.  
 *
 * Attribution: Based on NumberToWords by Srinivas Sampath
 *        
 *
 * Example:
 if number is 61: RETURNS: "sixty-first (61st)"
 If number is 15: RETURNS: "fifteenth (15th)"
 ****************************************************************/
 AS BEGIN
 
 DECLARE @inputNumber VARCHAR(38)
 DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(15))
 DECLARE @outputString VARCHAR(8000)
 DECLARE @length INT
 DECLARE @counter INT
 DECLARE @loops INT
 DECLARE @position INT
 DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
 DECLARE @tensones CHAR(2)
 DECLARE @tens CHAR(1)
 DECLARE @ones CHAR(1)
 DECLARE @nth varchar(2) = ''
 
 IF @Number > 99 Return 'Invalid: Value must be between 1 and 100'
 
/* Rerun if number or day is entered as a negative number */
 If @Number < 0  Return dbo.udf_DaysToWords (ABS(@Number))

 -- initialize the variables
 SELECT @inputNumber = CONVERT(varchar(38), @Number)
      , @outputString = ''
      , @counter = 1
 SELECT @length   = LEN(@inputNumber)
      , @position = LEN(@inputNumber) - 2
      , @loops    = LEN(@inputNumber)/3
 -- make sure there is an extra loop added for the remaining numbers
 IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
 -- insert data for the numbers and words
 INSERT INTO @NumbersTable   SELECT '00', ''
     UNION ALL SELECT '01', 'first'      UNION ALL SELECT '02', 'second'
     UNION ALL SELECT '03', 'third'    UNION ALL SELECT '04', 'fourth'
     UNION ALL SELECT '05', 'fifth'     UNION ALL SELECT '06', 'sixth'
     UNION ALL SELECT '07', 'seventh'    UNION ALL SELECT '08', 'eighth'
     UNION ALL SELECT '09', 'nineth'     UNION ALL SELECT '10', 'tenth'
     UNION ALL SELECT '11', 'eleventh'   UNION ALL SELECT '12', 'twelveth'
     UNION ALL SELECT '13', 'thirteenth' UNION ALL SELECT '14', 'fourteenth'
     UNION ALL SELECT '15', 'fifteenth'  UNION ALL SELECT '16', 'sixteenth'
     UNION ALL SELECT '17', 'seventeenth' UNION ALL SELECT '18', 'eighteenth'
     UNION ALL SELECT '19', 'nineteenth' UNION ALL SELECT '20', 'twentieth'
     UNION ALL SELECT '30', 'thirtieth' UNION ALL SELECT '40', 'fourtieth'
  UNION ALL SELECT '50', 'fiftieth' UNION ALL SELECT '60', 'sixtieth'
  UNION ALL SELECT '70', 'seventieth' UNION ALL SELECT '80', 'eightieth'
  UNION ALL SELECT '90', 'ninetieth'
 WHILE @counter <= @loops BEGIN
  -- get chunks of 3 numbers at a time, padded with leading zeros
  SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
  IF @chunk <> '000' BEGIN
   SELECT @tensones = SUBSTRING(@chunk, 2, 2)
        , @tens = SUBSTRING(@chunk, 2, 1)
        , @ones = SUBSTRING(@chunk, 3, 1)
   -- If twenty or less, use the word directly from @NumbersTable
   IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
    SET @outputString = (SELECT word 
                                       FROM @NumbersTable 
                                       WHERE @tensones = number)
                    + @outputString
       END
    ELSE BEGIN -- break down the ones and the tens separately
              SET @outputString = ' ' 
                             + (SELECT word 
                                     FROM @NumbersTable 
                                     WHERE @tens + '0' = number)
               + '-'
                              + (SELECT word 
                                     FROM @NumbersTable 
                                     WHERE '0'+ @ones = number)
                    + @outputString
   END
   
  END
  SELECT @counter = @counter + 1
       , @position = @position - 3
 END

 --Nth declaration
 SET @nth = CASE WHEN @Number BETWEEN 11 and 19 THEN 'th' ELSE
  CASE RIGHT(@Number,1) WHEN 1 THEN 'st' WHEN 2 THEN 'nd' WHEN 3 THEN 'rd' ELSE 'th' END
  END
 -- Remove any double spaces
 SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' '))) + ' (' + convert(varchar,@Number) + @nth +')'
 SET @outputstring = LEFT(@outputstring, 1) + SUBSTRING(@outputstring, 2, 8000)


 RETURN @outputString -- return the result
 END
GO

Hope this has been helpful. If so please mark below. Thanks

Things to do

Linear Fading Line CSS3

I was always impressed when I saw a separator line that faded on both sides like so:

---

I finally found out how to do this. Below is the code used to obtain this effect.  I have using HR as the container for the style.

CSS as follows:
hr{

    border: 0;
    height: 2px;
    margin:18px 0;
    position:relative;
    
background: -moz-linear-gradient(left, rgba(0,0,0,0) 0%, rgba(0,0,0,0) 10%, rgba(0,0,0,0.65) 50%, rgba(0,0,0,0) 90%, rgba(0,0,0,0) 100%); /* FF3.6+ */
    
background: -webkit-gradient(linear, left top, right top, color-stop(0%,rgba(0,0,0,0)), color-stop(10%,rgba(0,0,0,0)), color-stop(50%,rgba(0,0,0,0.65)), color-stop(90%,rgba(0,0,0,0)), color-stop(100%,rgba(0,0,0,0))); /* Chrome,Safari4+ */
    
background: -webkit-linear-gradient(left, rgba(0,0,0,0) 0%,rgba(0,0,0,0) 10%,rgba(0,0,0,0.65) 50%,rgba(0,0,0,0) 90%,rgba(0,0,0,0) 100%); /* Chrome10+,Safari5.1+ */
    
background: -o-linear-gradient(left, rgba(0,0,0,0) 0%,rgba(0,0,0,0) 10%,rgba(0,0,0,0.65) 50%,rgba(0,0,0,0) 90%,rgba(0,0,0,0) 100%); /* Opera 11.10+ */
    
background: -ms-linear-gradient(left, rgba(0,0,0,0) 0%,rgba(0,0,0,0) 10%,rgba(0,0,0,0.65) 50%,rgba(0,0,0,0) 90%,rgba(0,0,0,0) 100%); /* IE10+ */
    
background: linear-gradient(left, rgba(0,0,0,0) 0%,rgba(0,0,0,0) 10%,rgba(0,0,0,0.65) 50%,rgba(0,0,0,0) 90%,rgba(0,0,0,0) 100%); /* W3C */
    
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#00000000', endColorstr='#00000000',GradientType=1 ); /* IE6-9 */

}

hr:before {
    content: "";
    display: block;
    border-top: solid 1px #f9f9f9;
    width: 100%;
    height: 1px;
    position: absolute;
    top: 50%;
    z-index: 1;
}

Enjoy

MC

How to Create a Comma-Delimited Result set Using FOR XML PATH()

There has been many times that I have a result set that has multiple rows for one given column and I want to have all those results in one row; effectively reducing the output.

In this case we have an output of groups, users, and codes, respectively.  The preferred output would be one row per user code.  But as you can see there are several codes per user.

This is where the FOR XML PATH('') and STUFF() come into play.  How do we do this?

Here is the syntax for what we want to accomplish and then we'll go through it a little more detailed.

Stuff syntax:
STUFF( select statement, start index, length, character expression )

For XML Path syntax: 
FOR XML PATH(element)  in our case we want to put two single quotes in the for xml path parentheses.

FOR XML PATH('')
To get the data into the comma delimited result we start with FOR XML PATH(''):

Note that if you want the XML out you would not use ('') at the end of FOR XML PATH.  If you omit this then you will get an out put for that column like

<data ><row>, <row><data ..."

By adding the ('') you remove the XML wrapper elements.  You can read more on MSDN Books Online under FOR XML PATH.










STUFF()

Notice that we started the string with a comma.  You could end it with a comma if you choose to just changes how  the next phase works.  I choose to put it at the beginning and then trim it with STUFF().

Which brings us to cleanup by using STUFF().  By using stuff we can omit the leading comma from the result set giving us a cleaner output in case you want to parse it later. STUFF() is like using a range REPLACE().  Instead of replacing all instances of something we are only replacing a specified location or range of characters.



Lets look at the syntax and break it down:

• Syntax: STUFF(, , , )
• start index tells us where to begin replacing characters with character string.
• length tells us how many characters are we going to be affecting.
• character string tells us what we are going to be replacing or "stuffing" in place of the affected characters.  In our case we replaced only the leading comma giving us the follow result.

 NOTE:You only need to use STUFF() if you want to trim the result.  If you are satisfied with your result then you don't need to use this.  I am only using it to trim the leading comma.

AutoNumber Group Sections within a Group Record: Crystal Reports

         

I had an interesting situation the other day when attempting to consolidate redundant .rpt files. The only difference in these files was the inclusion of some paragraphs, sentences, or words within a given header. As we all know I do consulting and IT work for the Real Estate industry and a desire to combine lease agreements and not have an agreement for each state - making editing that reflected multiple states become an editing pain and nightmare - for ease in editing and maintenance/management. But with that each header has a roman numeral with the header, such as "III. RENT". When combining multiple states some sections may apply to one state and not another. For example, Utah, Arizona, etc allow for a lease initiation fee whereas California does not. With that in mind the "IV. LEASE INITIATION FEE" section would be suppressed for California and not for other states. Also the record set returned to the report was a single record - making auto incrementing a chore.
So let's address each issue.
We need to make a couple of assumptions first:

Only 1 record

Each paragraph in the agreement is its own Group Header (gh1a, gh1b, gh1c...)

Only Sections showing are Group Headers and Page Footer

Numbering should be consistent, regardless of suppressed sections

1 - Getting the report to auto increment and only on the headers that we want.

The problem here is that there really isn't anything to base a running total or change on N group/record. Adding a function to increment a variable won't work either as the function will only run once no matter how many times you place it into the report file. So what can we do? We need a function that will increment as many times as we need during the compiling pass through. We want to also use a simple function right? What about something like this:

    WhilePrintingRecords; //Used for convenience of using either Shared or Global Variables
    Shared NumberVar X:=X+1;

Sounds simple and should work for what we want - simply increment X for every header we place the function.  So I placed the function on each header that I wanted to increment and found that the function incremented for the first placement and not the remainder.  FRUSTRATING!!!

What I ended up having to do for single pass through report like this was create a function with the above code for every header and place at each header.  So header 1. would have
@1 - HEADER, next header @2 - HEADER, etc.

2 - What about identical header sections that have suppression based on some variable?

Remember though that we have some section that may vary slightly for each state but are in every agreement such as RENT.  In my consolidation I have 3 different RENT, LATE CHARGES, etc  sections.  How do I make sure the correct one is showing with the right number?  This was the simple part.  When you have multiple section that would have the same number you simply use the same function.  Therefore, when the report runs, the number would be the same for either section and would not get incremented more than once.

3 - But what about sections that are suppressed altogether? 
 This had me for a few minutes.  What I was seeing with the supplied function, when a section was suppressed outright - not because of multiple options as we discussed in the previous section - the function was still firing and incrementing X, resulting in a gap in the numbering.

To overcome this we need to overcome the suppression formula by adding the suppress formula to the increment formula. If the suppress formula is true then do not increment, otherwise do increment.

    WhilePrintingRecords;
    Shared NumberVar X;
    IF {field} = {value} //Suppress expression
    THEN X:=X
    ELSE X:=X+1;



Once all this was in place for each section that had some kind of suppression - again exclude for multiple option suppression - the numbering worked perfectly, allowing for multiple section options and suppressed sections.

Where it is a legal document it is common practice to use Roman Numerals when numbering sections.  To do that you simply use ROMAN().
Note: The use of ROMAN() changes a numeric value to a string value. 

My final coding(s) look like the following:

//EACH HEADER I DESIRE NUMBERING
    WhilePrintingRecords;
    Shared NumberVar X;
    Roman(X:=X+1);

//EACH HEADER THAT IS BEING SUPPRESSED BUT NOT PART OF A MULTIPLE OPTION

    WhilePrintingRecords;
    Shared NumberVar X;

    IF {field} = {value}
    THEN Roman(X)
    ELSE Roman(X:=X+1);


Please like this if you found it useful.