Format integer in SQL
Poster asked how to display an INTEGER with thousands separators (10000 as 10,000) directly from a SELECT. Replies noted plain SQL has no USING/format clause — that's available in 4GL or ACE reports, or you can post-process output with awk/perl. DECODE was suggested but only works per hard-coded value. Workarounds offered: a CASE expression using LENGTH plus SUBSTR/concatenation on the value cast to CHAR, and, better, a reusable stored function (sample code posted) that loops over the string inserting commas every three digits, called as select number_comma(col) — which solves the problem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Experts
Any idea how can I format the integer value from SQL to be seperated by comma
Say i have value 10000 in test table
my SQL :
select number
from test
currently it show me 10000, but i need to display as 10,000
Thanks and Regards
Soo Chee
2008/8/21 NG SOO CHEE <ng.soo.chee@gmail.com>:
> Hi Experts
>
> Any idea how can I format the integer value from SQL to be seperated by comma
>
> Say i have value 10000 in test table
> my SQL :
>
> select number>
> from test
>
> currently it show me 10000, but i need to display as 10,000
>
> Thanks and Regards
> Soo Chee
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Soo Chee
This can be done in either the ACE Report Writer or 4GL with use of
the 'USING' clause, however it is not possible in 'straight'
SQL. Possibilities are to pipe through nawk or perl to reformat the output.
Keith
Sorry if this is a duplicate post...
I tried this:
create table t1 (col1 int);
insert into t1 values (100000);
select col1, DECODE(col1, '100000', '100,000') as new from t1
and got...
col1 new
100000 100,000
Even better: select DECODE(col1, '100000', '100,000') as new from t1 where col1 > 1
The problem I see with Decode is it's only good for that specific value. When
the value is 100001 or any other 4+ digit number, it doesn't work.
Here is one brute force approach to incorporating comma seperation via SQL...
SQL...
create temp table t1 (col1 int);
insert into t1 values (1);
insert into t1 values (12);
insert into t1 values (123);
insert into t1 values (1234);
insert into t1 values (12345);
insert into t1 values (123456);
insert into t1 values (1234567);select
case
length(col1::char(15))
when 4 then substr(col1::char(4),1,1)||','||substr(col1::char(4),2,3)
when 5 then substr(col1::char(5),1,2)||','||substr(col1::char(5),3,3)
when 6 then substr(col1::char(6),1,3)||','||substr(col1::char(6),4,3)
when 7 then substr(col1::char(7),1,1)||','||substr(col1::char(7),2,3)
||','||substr(col1::char(7),5,3)
when 8 then substr(col1::char(8),1,2)||','||substr(col1::char(8),3,3)
||','||substr(col1::char(8),6,3)
else col1::char(15)
end comma_sep
from t1
Results:
comma_sep
1
12
123
1,234
12,345
123,456
1,234,567
So if that case clause is the core of a stored procedure, you could have
something like
select commas(col1) from.....
That way it can be done once and used for many columns. The commas
function would have an integer as input with a string as output. You
could also make other procedures, overloading the name, to handle floating
point numbers, strings, etc.
Dick Snoke
IBM Data Management - ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595
From:
"DAVE GRIFFEN" <dgriffen@finishline.com>
To:
ids@iiug.org
Date:
08/21/2008 01:50 PM
Subject:
Re: Format integer in SQL [13182]
The problem I see with Decode is it's only good for that specific value.
When
the value is 100001 or any other 4+ digit number, it doesn't work.
Here is one brute force approach to incorporating comma seperation via
SQL...
SQL...
create temp table t1 (col1 int);
insert into t1 values (1);
insert into t1 values (12);
insert into t1 values (123);
insert into t1 values (1234);
insert into t1 values (12345);
insert into t1 values (123456);
insert into t1 values (1234567);select
case
length(col1::char(15))
when 4 then substr(col1::char(4),1,1)||','||substr(col1::char(4),2,3)
when 5 then substr(col1::char(5),1,2)||','||substr(col1::char(5),3,3)
when 6 then substr(col1::char(6),1,3)||','||substr(col1::char(6),4,3)
when 7 then substr(col1::char(7),1,1)||','||substr(col1::char(7),2,3)
||','||substr(col1::char(7),5,3)
when 8 then substr(col1::char(8),1,2)||','||substr(col1::char(8),3,3)
||','||substr(col1::char(8),6,3)
else col1::char(15)
end comma_sep
from t1
Results:
comma_sep
1
12
123
1,234
12,345
123,456
1,234,567
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
CREATE function number_comma(i integer)
RETURNING varchar(20);
DEFINE len integer;
DEFINE str VARCHAR(20);
DEFINE res VARCHAR(20);
LET str = CAST(i as varchar(20));
LET len = length(str);
LET res = '';
WHILE len > 3
IF res <> '' THEN
LET res = substr(str, len - 2, 3) || ',' || res;
ELSE
LET res = substr(str, len - 2, 3);
END IF;
LET str = substr(str, 1, len - 3);
LET len = length(str);
END WHILE
IF res <> '' THEN
LET res = str || ',' || res;
ELSE
LET res = str;
END IF;
RETURN res;
END function ;
select number_comma(1234567) from systables where tabid = 1
(expression)
1,234,567
Jairo Gubler
Richard Snoke escreveu:
So if that case clause is the core of a stored procedure, you could have
something like
select commas(col1) from.....
That way it can be done once and used for many columns. The commas
function would have an integer as input with a string as output. You
could also make other procedures, overloading the name, to handle floating
point numbers, strings, etc.
Dick Snoke
IBM Data Management - ChannelWorks
[1]dsnoke@us.ibm.com
(404) 487-1595
From:
"DAVE GRIFFEN" [2]<dgriffen@finishline.com>
To:
[3]ids@iiug.org
Date:
08/21/2008 01:50 PM
Subject:
Re: Format integer in SQL [13182]
The problem I see with Decode is it's only good for that specific value.
When
the value is 100001 or any other 4+ digit number, it doesn't work.
Here is one brute force approach to incorporating comma seperation via
SQL...
SQL...
create temp table t1 (col1 int);
insert into t1 values (1);
insert into t1 values (12);
insert into t1 values (123);
insert into t1 values (1234);
insert into t1 values (12345);
insert into t1 values (123456);
insert into t1 values (1234567);
select
case
length(col1::char(15))
when 4 then substr(col1::char(4),1,1)||','||substr(col1::char(4),2,3)
when 5 then substr(col1::char(5),1,2)||','||substr(col1::char(5),3,3)
when 6 then substr(col1::char(6),1,3)||','||substr(col1::char(6),4,3)
when 7 then substr(col1::char(7),1,1)||','||substr(col1::char(7),2,3)
||','||substr(col1::char(7),5,3)
when 8 then substr(col1::char(8),1,2)||','||substr(col1::char(8),3,3)
||','||substr(col1::char(8),6,3)
else col1::char(15)
end comma_sep
from t1
Results:
comma_sep
1
12
123
1,234
12,345
123,456
1,234,567
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
--
Esta mensagem foi verificada pelo sistema de antivírus e
acredita-se estar livre de perigo.
References
1. mailto:dsnoke@us.ibm.com
2. mailto:dgriffen@finishline.com
3. mailto:ids@iiug.org