How do you remove leading zero's from a string?
Is there an easy way to remove leading zero's from a string usinga derived field task? I would think SSIS has a easy way of doing this. Something like a ltrim but for zero's???
August 5th, 2008 6:54pm

Nope. Simplest method woud be to convert to a numeric or integer type and then back to a string, e.g (DT_WSTR,10)(DT_I4)[StringVar]
Free Windows Admin Tool Kit Click here and download it now
August 5th, 2008 7:59pm

Any suggestions for why that might not work? I'm currently in the process of upgrading an old DTS package to SSIS. The old packge ran with no issues, pulling data from a flatfile. The field that is giving me fits is QUANT and, in the flatfile is basically [arbitrary number of leading 0s]120 orsomething similar. And it just will not convert to numeric or integer or anything, continually giving me the familiar 0xC0049064 error. Is there a better way to trim 0s, or am I going to have to write a script?
September 18th, 2008 12:16am

Replace(Ltrim(Replace(column1, '0', ' ')), ' ', '0') this will remove your leading 0's but it will fail if your string consist of any space in between.
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2010 11:57am

Another Replace statement using some character which usually doesnt come in the data will suffice the need. Something like - Replace(Ltrim(Replace(column1, '0', ' ')), ' ', '0') replace(replace(ltrim(replace(replace(ltrim(column1), ' ', '¬'), '0', ' ') ), ' ', '0') ,'¬', ' ') Yeah, its lengthy :)
May 26th, 2011 5:49pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics