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

Prardhan N
Prardhan N used Ask the Experts™
on
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!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try BIGINT

select CAST(cast(amount as VARCHAR(4000 OCTETS)) AS BIGINT) from schemaname.tablename where pay_id=1038 with ur ;
Database Expert
Awarded 2016
Top Expert 2016
Commented:
or this -

select replace(replace(rtrim(ltrim(replace(char(amount), '0', ' '))), ' ', '0'), '.', '') from schemaname.tablename where pay_id=1038 with ur ;
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Another option could be-

select  TRIM( 'T',0, TRIM( 'L' ,0, CAST( amount as VARCHAR(4000 OCTETS) ) ) )
from schemaname.tablename where pay_id=1038 with ur ;

Author

Commented:
Thanks for inputs. working on inputs.

Author

Commented:
Thanks for help!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial