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]
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.
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