Need help on Type casting and removing of leading and trailing Zeros for amount column(Decimal) in DB2.

Sri Prardhan

Need help on Type casting and removing of leading and trailing Zeros for amount column(Decimal) in DB2.

Need help on Type casting and removing of leading and trailing Zeros for amount column(Decimal) in DB2.

select cast(amount as VARCHAR(4000 OCTETS)) from schemaname.tablename where pay_id=1038 with ur ;

Casting function is not removing the trailing zeros.

Can you please suggest how to remove the leading and trailing zeros.

Thanks in advance!!!

Chris Hoelscher

Need help on Type casting and removing of leading and trailing Zeros for amount column(Decimal) in D
(in response to Sri Prardhan)
What platform what, release / mode – what bif compatibility zparm setting?

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
[Description: Description: cid:[login to unmask email]
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Sri Prardhan [mailto:[login to unmask email]
Sent: Monday, November 20, 2017 6:18 PM
To: [login to unmask email]
Subject: [DB2-L] - Need help on Type casting and removing of leading and trailing Zeros for amount column(Decimal) in DB2.


Need help on Type casting and removing of leading and trailing Zeros for amount column(Decimal) in DB2.

select cast(amount as VARCHAR(4000 OCTETS)) from schemaname.tablename where pay_id=1038 with ur ;

Casting function is not removing the trailing zeros.

Can you please suggest how to remove the leading and trailing zeros.

Thanks in advance!!!

-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.
Attachments

  • image001.jpg (<1k)

Greg Palgrave

RE: Need help on Type casting and removing of leading and trailing Zeros for amount column(Decimal) in DB2.
(in response to Sri Prardhan)

Hi Sri Prardhan,

Both LUW and z/OS (depending on the versions) DB2 have TRIM and TO_CHAR functions:

 db2 values "TRIM('0' FROM TO_CHAR(987654.00, '999,999,999.99'))  "

1                                 
-----------------------------
     987,654.                

 

That may not be exactly what you need, but it might give you a start. Many formatting options on the TO_CHAR function that may be useful too.

You can also just TRIM the decimal value directly:

 

db2 values "TRIM('0' FROM 00987654.00)  "

1
------------
987654.

 

Cheers

Greg


In Reply to Sri Prardhan:

Need help on Type casting and removing of leading and trailing Zeros for amount column(Decimal) in DB2.