Have to remove 0's from varchar field

Amarpreet · Post by **Amarpreet** » Thu Jun 15, 2006 7:52 am

I have a varchar field, which contains values like 00234,00980,.....

I have to remove all the 0's from the left of this value. my result should be 234,980,......
which function should I use in Datastage to achieve this?

DSguru2B · Post by **DSguru2B** » Thu Jun 15, 2006 7:57 am

Doing an arithmatic manipulation on a charcter forces it to be changed to an integer. Just try multiplying it by 1 that should work.

ArndW · Post by **ArndW** » Thu Jun 15, 2006 8:04 am

You can also do a TRIM(In.Column,'0','L') which will remove all leading "0" characters from your column.

rwierdsm · Post by **rwierdsm** » Thu Jun 15, 2006 8:44 am

Assuming the data is as shown in your post, you will have to separate the data based on the ',' delimiter before implementing the solutions suggested by Arnd and Guru. Rebuild the string, putting the ',' back in after the manipulation.

Rob

kumar_s · Post by **kumar_s** » Thu Jun 15, 2006 9:01 am

rwierdsm wrote:Assuming the data is as shown in your post, you will have to separate the data based on the ',' delimiter before implementing the solutions suggested by Arnd and Guru. Rebuild the string, putting the ',' back in after the manipulation.

Rob

It is assumed, Amarpreet has given the values for single filed in order seperated by comma and not all together a single field value.

Amarpreet · Post by **Amarpreet** » Thu Jun 15, 2006 9:01 am

Thanks, its working

Amarpreet · Post by **Amarpreet** » Thu Jun 15, 2006 11:52 pm

Yes those were 2 separate values(just exmaple values). Used trim function, which is working just perfectly. Thanks a ton.....