Posted: Thu May 22, 2014 6:12 am Post subject: How to format BIGINT with Commas
I was wondering if there was a better way in DB2 z/OS to format a number with commas. In other words, how would you code a query to change a number from 3432149142 to 3,432,149,142 ?
I've developed a kludgy way of doing it, but was hoping for something more elegant. Currently it only handles numbers less than 10 billion.
Code:
CASE WHEN TOTALROWS >= 1000000000
THEN CHAR(COALESCE(
SUBSTR(STRIP(CHAR(TOTALROWS)),1,1)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),2,3)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),5,3)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),8,3),'-1'))
WHEN TOTALROWS >= 100000000
THEN CHAR(COALESCE(
SUBSTR(STRIP(CHAR(TOTALROWS)),1,3)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),4,3)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),7,3),'-1'))
WHEN TOTALROWS >= 10000000
THEN CHAR(COALESCE(
SUBSTR(STRIP(CHAR(TOTALROWS)),1,2)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),3,3)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),6,3),'-1'))
WHEN TOTALROWS >= 1000000
THEN CHAR(COALESCE(
SUBSTR(STRIP(CHAR(TOTALROWS)),1,1)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),2,3)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),5,3),'-1'))
WHEN TOTALROWS >= 100000
THEN CHAR(COALESCE(
SUBSTR(STRIP(CHAR(TOTALROWS)),1,3)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),4,3),'-1'))
WHEN TOTALROWS >= 10000
THEN CHAR(COALESCE(
SUBSTR(STRIP(CHAR(TOTALROWS)),1,2)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),3,3),'-1'))
WHEN TOTALROWS >= 1000
THEN CHAR(COALESCE(
SUBSTR(STRIP(CHAR(TOTALROWS)),1,1)
||','||SUBSTR(STRIP(CHAR(TOTALROWS)),2,3),'-1'))
ELSE CHAR(INTEGER(COALESCE(TOTALROWS,-1)))
END AS TOTALROWS
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
Posted: Thu May 22, 2014 12:57 pm Post subject:
RoyKMathur,
There is a simpler way to do it. How is TOTALROWS column defined in the table? Is it a Colum in the table or is it an output from a scalar function? I posted something similar here
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
Posted: Thu May 22, 2014 3:27 pm Post subject:
Deleted as it does NOT handle negative numbers _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Before I posted I had spent time reviewing the manual and found nothing. I read right past TO_CHAR because it said it was for TIMESTAMPS... And because my number was a BIGINT, not a VARCHAR, I never even looked at VARCHAR_FORMAT. Thanks again!
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
Just realized that with DB2 10 version it is quite simple
