Formatting numbers in SQL

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.

 

• SQL function VARCHAR_FORMAT
• SQL function TO_CHAR

 

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