IDS Forum

Re: Remove trailing zeros in SQL

Posted By: John Miller iii
Date: Wednesday, 7 May 2008, at 1:38 a.m.

In Response To: Re: Remove trailing zeros in SQL (Long Nguyen)

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.

=

Messages In This Thread

• Remove trailing zeros in SQL
  Long Nguyen -- Tuesday, 6 May 2008, at 11:20 p.m.
  • Re: Remove trailing zeros in SQL
    EDCEL BARCENA -- Tuesday, 6 May 2008, at 11:54 p.m.
    • Re: Remove trailing zeros in SQL
      Long Nguyen -- Wednesday, 7 May 2008, at 12:22 a.m.
      • Re: Remove trailing zeros in SQL
        Long Nguyen -- Wednesday, 7 May 2008, at 1:03 a.m.
        • Re: Remove trailing zeros in SQL
          John Miller iii -- Wednesday, 7 May 2008, at 1:38 a.m.
        • Re: Remove trailing zeros in SQL
          Jonathan Leffler -- Wednesday, 7 May 2008, at 1:46 a.m.
  • Re: Remove trailing zeros in SQL
    GARY GU -- Wednesday, 7 May 2008, at 1:40 a.m.
    • Re: Remove trailing zeros in SQL
      Long Nguyen -- Wednesday, 7 May 2008, at 2:22 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.