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!!!
Prardhan NAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
or this -

select replace(replace(rtrim(ltrim(replace(char(amount), '0', ' '))), ' ', '0'), '.', '') from schemaname.tablename where pay_id=1038 with ur ;
0
 
Pawan KumarDatabase ExpertCommented:
Please try BIGINT

select CAST(cast(amount as VARCHAR(4000 OCTETS)) AS BIGINT) from schemaname.tablename where pay_id=1038 with ur ;
0
 
Pawan KumarDatabase ExpertCommented:
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 ;
0
 
Prardhan NAuthor Commented:
Thanks for inputs. working on inputs.
0
 
Prardhan NAuthor Commented:
Thanks for help!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.