Tuesday, December 1, 2020

Formatting numbers in SQL

adding thousand separators in SQL numbers

I am sure I am not the only person when I get a number returned in my SQL results I have to look at it carefully to determine is that number millions, billions, trillions, or even bigger?

SELECT SUPPORTED_VALUE 
  FROM QSYS2.SQL_SIZING
 WHERE SIZING_ID = 18303


SUPPORTED_VALUE
---------------------
 18446744073709551600

For an explanation of what the SQL View SQL_SIZING shows see here.

Those of us who have been programming for any time in IBM i have formatted numbers in DDS files using edit codes and edit words in the source:


AAN01N02N03T.Name++++++RLen++TDpBLinPosFunctions+++++++++++++++++++++++++
A          R SCREEN
A            FIRST         20Y 0O  3  2EDTCDE(J)
A            SECOND        20Y 0O  4  2EDTWRD('    -    -    -    -  0 ')



 18,446,744,073,709,551,600
 1844-6744-0737-0955-1600

Using an Edit word, see field SECOND, I can use other characters to format the number, not necessarily into a recognizable format. Whatever I do to these fields they remain numbers.

In modern RPG I have two Built in Functions that allow me to edit numbers. Alas, I have to output to a character type variable.

01  **free
02  dcl-s First char(30) ;
03  dcl-s Second char(30) ;
04  dcl-c BigNbr const(18446744073709551600) ;

05  First = %editc(BigNbr:'J') ;
06  Second = %editw(BigNbr:
             '    -    -    -    -  0 ') ;

07  dsply First ;
08  dsply Second ;

09  *inlr = *on ;

Line 1: I did say this example was going to be modern RPG, there are no columns in modern RPG.

Lines 2 and 3: Definitions for the variables that will contain the output from the BiFs.

Line 4: Definition of a constant that contains the big number we have been using in these examples.

Line 5: Format the number using the Edit Code BiF, %EDITC, using edit code "J".

Line 6: Format the number using the Edit Word BiF, %EDITW, using the same format as I did in the display file. I have wrapped this line to second line so that it will fit on this page.

Lines 7 and 8: Use the Display operation code, DSPLY, to show the contents of the two variables.

When the two DSPLY are executed I see:

DSPLY  18,446,744,073,709,551,600
DSPLY  1844-6744-0737-0955-1600

What can I do with SQL?

Alas, there is no edit code or edit word functionality in Db2 for i like there is in DDS or modern RPG. There are a couple of SQL functions I can use to format a number with comma separators:

SELECT SUPPORTED_VALUE AS "Original number",
       VARCHAR_FORMAT(SUPPORTED_VALUE,'999,999,999,999,999,999,999')
         AS "VARCHAR_FORMAT",
       TO_CHAR(SUPPORTED_VALUE,'999G999G999G999G999G999G999')
         AS "TO_CHAR"
  FROM QSYS2.SQL_SIZING
 WHERE SIZING_ID = 18303


Original number       VARCHAR_FORMAT              TO_CHAR
--------------------  --------------------------  --------------------------
18446744073709551600  18,446,744,073,709,551,600  18,446,744,073,709,551,600

I have used two SQL functions in this statement: VARCHAR_FORMAT and TO_CHAR. They do the same things, I just prefer to use VARCHAR_FORMAT. In the VARCHAR_FORMAT function I gave commas ( , ) as the thousand separators. As I am in the USA we use the comma as the separator. With the TO_CHAR I have used "G" as the separator character, which results in the separator character being the default thousand separator character.

If I substitute the commas or "G" with another character I get the following error:

SELECT VARCHAR_FORMAT(SUPPORTED_VALUE,'9999-9999-9999-9999-9999')
  FROM QSYS2.SQL_SIZING
 WHERE SIZING_ID = 18303


SQL State: 22018
Vendor Code: -20476 
Message: [SQ20476] Format string for function VARCHAR_FORMAT not valid.
Cause . . . . . :   The format string 9999-9999-9999-9999-9999 specified 
for function VARCHAR_FORMAT is not valid.

But I can put commas in those places instead using the "G":

SELECT VARCHAR_FORMAT(SUPPORTED_VALUE,'9999G9999G9999G9999G9999')
  FROM QSYS2.SQL_SIZING
 WHERE SIZING_ID = 18303


00001
-------------------------
 1844,6744,0737,0955,1600

There are other things I can do with numbers and the VARCHAR_FORMAT:

SELECT VARCHAR_FORMAT(1234.56,'999G999G999D999') AS "1",
       VARCHAR_FORMAT(1234.56,'000G000G000D000') AS "2",
       VARCHAR_FORMAT(-1234.56,'999G999G999D999') AS "3",
       VARCHAR_FORMAT(-1234.56,'999G999G999D999MI') AS "4",
       VARCHAR_FORMAT(1234.56,'S999G999G999D999') AS "5",
       VARCHAR_FORMAT(-1234.56,'S999G999G999D999') AS "6",
       VARCHAR_FORMAT(1234.56,'L999G999G999D999') AS "7",
       TO_CHAR(-1234.56,'L999G999G999D999') AS "8"
  FROM SYSIBM.SYSDUMMY1

Rather than display the results in columns I am going to show them in rows so that they will fit in the width here:

1 =       1,234.560
2 = 000,001,234.560
3 =      -1,234.560
4 =       1,234.560-
5 = +      1,234.560
6 = -      1,234.560
7 = $       1,234.560
8 = $      -1,234.560

The first thing to notice is that all of the results, except number 2, have leading spaces. I could have changed the length of the formatting string to match the size of the value. But if this was a real life scenario I would need my results in the results column to align to the right, whether the number of digits is 6 or 14:

SELECT VARCHAR_FORMAT(NUMBER,'999G999G999G999D999MI')
         AS "Formatted number"
  FROM MYLIB.TESTFILE


Formatted number
--------------------
          1,234.560 
 12,345,678,912.000 
          1,234.560-

Back to my column of results…

Result 2: This has leading zeroes as I replaced the 9s in the formatting string with 0s. Therefore, the leading zeroes are preserved.

Result 3: A negative value without any special formatting has the minus sign as the leading character.

Result 4: If I want a minus sign at the end of the string I must use MI at the end of the formatting string.

Result 5: The S character inserts the sign character at the start of the formatted string, regardless of the number of characters. Here a positive number results in a plus sign at the start of the string.

Result 6: Same formatting as result 5, but with a negative number has a minus sign at the start of the result.

Result 7: The L character is used to denote the default currency symbol. Here it is a dollar sign ( $ ). This is shown at the start of the result.

Result 8: Same as result 7, but for a negative amount. As I did not give a code for the where the minus sign is to display this is the same happens as it did with result 3. I was sneaky here using the TO_CHAR rather than the VARCHAR_FORMAT.

If I needed to remove the leading spaces from any of the results I could use the Left Trim function, LTRIM, like this.

SELECT LTRIM(VARCHAR_FORMAT(1234.56,'999G999G999D999')) AS "1",
       LTRIM(VARCHAR_FORMAT(-1234.56,'999G999G999D999')) AS "3",
       LTRIM(VARCHAR_FORMAT(-1234.56,'999G999G999D999MI')) AS "4"
  FROM SYSIBM.SYSDUMMY1

1 = 1,234.560
3 = -1,234.560
4 = 1,234.560-

Now I can use the VARCHAR_FORMAT in my results and see if that number is really trillions.

 

 

This article was written for IBM i 7.4, and should work for IBM i 7.3 TR5 and 7.2 TR9 and later.

Written by at
Labels: ,

1 comment:

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.