Convert Integer to Char while selecting in a DB2 query

gauravgupta2808 · Posted: Wed Oct 17, 2007 5:37 pm

Hi,

I need to convert an integer to char while selecting in a DB2 query.

I have used CHAR(integer), but the problem with this is that it removes the leading zeroes from the integer.

Ex : If integer = 01001 then i get CHAR(integer) = 1001.
But i need to retain the leading zeroes.
Can you please help me with this.

Srihari Gonugunta · Posted: Wed Oct 17, 2007 5:52 pm

Gaurav,
Try DIGITS function

gauravgupta2808 · Posted: Wed Oct 17, 2007 6:03 pm

Hi Srihari,

Thanks for the suggestion.

But DIGITS function is adding more leading Zeroes.

If the value of integer = 010004 DIGITS(integer) = 0000010004

If value of integer = 504045, DIGITS(integer) = 0000504045

I donot want the extra zeroes ....

Could you please suggest some way out.

Srihari Gonugunta · Posted: Thu Oct 18, 2007 1:00 pm

Hi Gaurav,

Try the following things.
1) SUBSTR(DIGITS(integer),5,6)) .....only when you are sure that your column always contain 6 digits
2) CAST(Integer as CHAR(10)) this holds good for your case, I suppose.

Please try and let us know if you face some problem

gauravgupta2808 · Posted: Mon Oct 22, 2007 9:12 pm

Thanks Srihari...

Option 1 works...
Option 2 removes the leading zeroes, but in my case i required the leading zero..... As i do a concatenate also

Thank you so much

Srihari Gonugunta · Posted: Tue Oct 23, 2007 3:27 pm

Gaurav,
Glad to know that it helped you...