Remove trailing zeros in SQL
A user concatenating a DECIMAL(12) quantity with text columns got output like "20.0000000000000000 LT DR" and wanted "20 LT DR" (and "2.5" rather than "2"). TRUNC() was suggested first but it dropped the fractional part. The accepted solution was to cast the number to a string and strip trailing characters, e.g. TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM qty||'')) (or qty::varchar(20)). Jonathan Leffler also suggested ROUND(), a stricter DECIMAL(x,y) type, or doing the formatting in the application instead of SQL. The poster adopted the double-TRIM approach.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all SQL gurus, I execute a SQL statement like this: 'select stiinvtr.item_code, stiinvtr.r_cont_qty||' '||stiinvtr.r_cont_uom||' '||stiinvtr.stock_unit UOM from stiinvtr where stiinvtr.item_code = "1002880"' and the result is: item_code uom 1002880 20.0000000000000000 LT DR How can I truncate all the zeros after 20 so that the result of those concatenated fields will be "20 LT DR".? Or any workaround to trim this numeric field's result? r_cont_qty dec(12) r_cont_umo char(2) stock_unit char(2) (It may be a simple question because my memory is getting old!!:). Can do this in 4GL but don't remember how to do in SQL!!) I am aware that the trim() function only applies to char fields. Regards, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind.
Hi,
You can use the trunc function as follows:
select stiinvtr.item_code, trunc(stiinvtr.r_cont_qty)||''||stiinvtr.r_cont_uom||' '||stiinvtr.stock_unit UOM
from stiinvtr
where stiinvtr.item_code = "1002880"'
Regards,
Edcel
Thanks Edcel,
Also Paul told me about the cast function.
Anyway thanks IIUG guys.
Long N
==========================
"EDCEL BARCENA"
<edcel_barcena@ho To: ids@iiug.org
tmail.com> cc:
Sent by: Subject: Re: Remove trailing zeros in SQL [11990]
ids-bounces@iiug.
org
07/05/2008 01:54
PM
Please respond to
ids
Hi,
You can use the trunc function as follows:
select stiinvtr.item_code, trunc(stiinvtr.r_cont_qty)||''||stiinvtr.r_cont_uom||' '||stiinvtr.stock_unit UOM
from stiinvtr
where stiinvtr.item_code = "1002880"'
Regards,
Edcel
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Disclaimer:
This correspondence is for the named person's use only. It may contain
confidential or legally privileged information or both. No confidentiality
or privilege is waived or lost by any mistransmission. If you receive this
correspondence in error, please immediately delete it together with any
attachments from your system and notify the sender. You must not disclose,
copy or rely on any part of this correspondence if you are not the intended
recipient.
Any opinions expressed in this message are those of the individual sender,
except where the sender expressly, and with authority, states them to be
the opinions of Ruralco Holdings Limited or any of its subsidiaries
(collectively "Ruralco").
Although all care has been taken to screen this communication for viruses,
neither the sender nor Ruralco warrants that any communication via the
Internet is free of errors, viruses, interception or interference.
Information is distributed without warranties of any kind.
Sorry you guys,
I still have a problem:
select stiinvtr.item_code, stiinvtr.r_cont_qty||' '||stiinvtr.r_cont_uom||''||s
tiinvtr.stock_unit UOM
from stiinvtr
where stiinvtr.item_code = "9448497";
The result:
item_code uom
9448497 2.5000000000000000 KG BG
I want to display uom as "2.5 KG BG"
If I use trunc():
select stiinvtr.item_code, trunc(stiinvtr.r_cont_qty)||' '||stiinvtr.r_cont_uom||' '||stiinvtr.stock_unit UOM
from stiinvtr
where stiinvtr.item_code = "9448497";
Then I will get:
item_code uom
9448497 2 KG BG
Which removed 0.5, which I don't want!.
Can the cast() with some format parameters give me 2.5 or 2.50 which I
want?.
Long N
======================================================
"Long Nguyen"
<lnguyen@ruralco. To: ids@iiug.org
com.au> cc:
Sent by: Subject: Re: Remove trailing zeros in SQL [11992]
ids-bounces@iiug.
org
07/05/2008 02:22
PM
Please respond to
ids
Thanks Edcel,
Also Paul told me about the cast function.
Anyway thanks IIUG guys.
Long N
==========================
"EDCEL BARCENA"
<edcel_barcena@ho To: ids@iiug.org
tmail.com> cc:
Sent by: Subject: Re: Remove trailing zeros in SQL [11990]
ids-bounces@iiug.
org
07/05/2008 01:54
PM
Please respond to
ids
Hi,
You can use the trunc function as follows:
select stiinvtr.item_code, trunc(stiinvtr.r_cont_qty)||''||stiinvtr.r_cont_uom||' '||stiinvtr.stock_unit UOM
from stiinvtr
where stiinvtr.item_code = "1002880"'
Regards,
Edcel
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Disclaimer:
This correspondence is for the named person's use only. It may contain
confidential or legally privileged information or both. No confidentiality
or privilege is waived or lost by any mistransmission. If you receive this
correspondence in error, please immediately delete it together with any
attachments from your system and notify the sender. You must not disclose,
copy or rely on any part of this correspondence if you are not the intended
recipient.
Any opinions expressed in this message are those of the individual sender,
except where the sender expressly, and with authority, states them to be
the opinions of Ruralco Holdings Limited or any of its subsidiaries
(collectively "Ruralco").
Although all care has been taken to screen this communication for viruses,
neither the sender nor Ruralco warrants that any communication via the
Internet is free of errors, viruses, interception or interference.
Information is distributed without warranties of any kind.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Disclaimer:
This correspondence is for the named person's use only. It may contain
confidential or legally privileged information or both. No confidentiality
or privilege is waived or lost by any mistransmission. If you receive this
correspondence in error, please immediately delete it together with any
attachments from your system and notify the sender. You must not disclose,
copy or rely on any part of this correspondence if you are not the intended
recipient.
Any opinions expressed in this message are those of the individual sender,
except where the sender expressly, and with authority, states them to be
the opinions of Ruralco Holdings Limited or any of its subsidiaries
(collectively "Ruralco").
Although all care has been taken to screen this communication for viruses,
neither the sender nor Ruralco warrants that any communication via the
Internet is free of errors, viruses, interception or interference.
Information is distributed without warranties of any kind.
This is not pretty but it will do what you want:
select stiinvtr.item_code,
TRIM( TRAILING '.' FROM TRIM( TRAILING '0' FROM (
stiinvtr.r_cont_qty::varchar(20))))||' '||stiinvtr.r_cont_uom||'
'||s
tiinvtr.stock_unit UOM
from stiinvtr
where stiinvtr.item_code =3D "9448497";
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS) -
http://www.ibm.com/software/data/informix/ids/
=
"Long Nguyen" =
<lnguyen@ruralco. =
com.au> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
Re: Remove trailing zeros in SQL=
05/06/2008 10:03 [11993] =
PM =
=
=
Please respond to =
ids@iiug.org =
=
=
Sorry you guys,
I still have a problem:
select stiinvtr.item_code, stiinvtr.r_cont_qty||' '||stiinvtr.r_cont_uo=m||'
'||s
tiinvtr.stock_unit UOM
from stiinvtr
where stiinvtr.item_code =3D "9448497";
The result:
item_code uom
9448497 2.5000000000000000 KG BG
I want to display uom as "2.5 KG BG"
If I use trunc():
select stiinvtr.item_code, trunc(stiinvtr.r_cont_qty)||' '||stiinvtr.r_cont_uom||' '||stiinvtr.stock_unit UOM
from stiinvtr
where stiinvtr.item_code =3D "9448497";
Then I will get:
item_code uom
9448497 2 KG BG
Which removed 0.5, which I don't want!.
Can the cast() with some format parameters give me 2.5 or 2.50 which I
want?.
Long N
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
"Long Nguyen"
<lnguyen@ruralco. To: ids@iiug.org
com.au> cc:
Sent by: Subject: Re: Remove trailing zeros in SQL [11992]
ids-bounces@iiug.
org
07/05/2008 02:22
PM
Please respond to
ids
Thanks Edcel,
Also Paul told me about the cast function.
Anyway thanks IIUG guys.
Long N
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
"EDCEL BARCENA"
<edcel_barcena@ho To: ids@iiug.org
tmail.com> cc:
Sent by: Subject: Re: Remove trailing zeros in SQL [11990]
ids-bounces@iiug.
org
07/05/2008 01:54
PM
Please respond to
ids
Hi,
You can use the trunc function as follows:
select stiinvtr.item_code, trunc(stiinvtr.r_cont_qty)||''||stiinvtr.r_cont_uom||' '||stiinvtr.stock_unit UOM
from stiinvtr
where stiinvtr.item_code =3D "1002880"'
Regards,
Edcel
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
Disclaimer:
This correspondence is for the named person's use only. It may contain
confidential or legally privileged information or both. No confidential=
ity
or privilege is waived or lost by any mistransmission. If you receive t=
his
correspondence in error, please immediately delete it together with any=
attachments from your system and notify the sender. You must not disclo=
se,
copy or rely on any part of this correspondence if you are not the inte=
nded
recipient.
Any opinions expressed in this message are those of the individual send=
er,
except where the sender expressly, and with authority, states them to b=
e
the opinions of Ruralco Holdings Limited or any of its subsidiaries
(collectively "Ruralco").
Although all care has been taken to screen this communication for virus=
es,
neither the sender nor Ruralco warrants that any communication via the
Internet is free of errors, viruses, interception or interference.
Information is distributed without warranties of any kind.
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
Disclaimer:
This correspondence is for the named person's use only. It may contain
confidential or legally privileged information or both. No confidential=
ity
or privilege is waived or lost by any mistransmission. If you receive t=
his
correspondence in error, please immediately delete it together with any=
attachments from your system and notify the sender. You must not disclo=
se,
copy or rely on any part of this correspondence if you are not the inte=
nded
recipient.
Any opinions expressed in this message are those of the individual send=
er,
except where the sender expressly, and with authority, states them to b=
e
the opinions of Ruralco Holdings Limited or any of its subsidiaries
(collectively "Ruralco").
Although all care has been taken to screen this communication for virus=
es,
neither the sender nor Ruralco warrants that any communication via the
Internet is free of errors, viruses, interception or interference.
Information is distributed without warranties of any kind.
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
Please try: TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM stiinvtr.r_cont_qty||'')) Regards, Gary
On Tue, May 6, 2008 at 10:03 PM, Long Nguyen <lnguyen@ruralco.com.au> wrote:
> Sorry you guys,
> I still have a problem:
> select stiinvtr.item_code, stiinvtr.r_cont_qty||' '||stiinvtr.r_cont_uom||'> '||s
> tiinvtr.stock_unit UOM
> from stiinvtr
> where stiinvtr.item_code = "9448497";
> The result:
> item_code uom
>
> 9448497 2.5000000000000000 KG BG
> I want to display uom as "2.5 KG BG"
> If I use trunc():
> select stiinvtr.item_code, trunc(stiinvtr.r_cont_qty)||' '> ||stiinvtr.r_cont_uom||' '||stiinvtr.stock_unit UOM
> from stiinvtr
> where stiinvtr.item_code = "9448497";
> Then I will get:
> item_code uom
>
> 9448497 2 KG BG
> Which removed 0.5, which I don't want!.
> Can the cast() with some format parameters give me 2.5 or 2.50 which I
> want?.
At somewhere about here, it becomes better to select the raw data -
the quantity, the unit of measure and the stock unit - as separate
columns, and deal with the formatting issues in the program rather
than trying to use the SELECT statement as a formatting tool.
Alternatively, instead of TRUNC, use ROUND(r_cont_qty, 2) to preserve
2 decimal places.
Or revise the type of r_cont_qty from FLOAT to DECIMAL(x,y) for
appropriate values of x and y (such as DECIMAL(6,2)).
Or, you can get really fancy and use a CASE to compute different
values for the number of digits after the decimal point in the ROUND
or TRUNC function depending on the UOM and stock unit.
Or you could include that information in a column in the table.
Or ...well, I very much doubt those are the only options.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Thanks for all yr help. The report my boss wants from a SQL is: if qty (dec 12) is 2.00000 then display as 2; if 2.5000000 then 2.5; if 2.67800000 then 2.678 So I will choose this TRIM(TRAILING '.' FROM TRIM.(TRAILING '0' FROM stiinvtr.r_cont_qty||'')) Cheers, Long N ==================================================================== "GARY GU" <gary_gu@engin.co To: ids@iiug.org m.au> cc: Sent by: Subject: Re: Remove trailing zeros in SQL [11995] ids-bounces@iiug. org 07/05/2008 03:40 PM Please respond to ids Please try: TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM stiinvtr.r_cont_qty||'')) Regards, Gary ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind.