SSIS Derived column trailing zeros -

im having issue derived column. have flat file source contains delimited data.

some of columns currencies , data saved in strange format (older systems tend way).

anyways, data saved such: 00030.200, 0123.410,0002231.210

i need convert these strings currency dt_cy. i've tried direct casts using difference numerical types fail (not sure if leading or trailing zeros causing it)

so question simple. expression can use in derived column remove leading , trailing zeros. note data not same length time can't left/right substring options.

any help?

tx

you have done way, using derived column:

replace(ltrim(replace(datatoconvert, "0", " ")), " ", "0") 

basically, convert zeros white spaces take benefits of ltrim function.

i founded solution here.

here example of how proceed: (let's want convert 0040.300. purpose, can display number as: |0|0|4|0|.|3|0|0| see how replace statement affects our data)

basically, conversion of 0040.300 operate way:

• inner replace: | | |4| |.|3| | | ('0' become space)
• ltrim: |4| |.|3| | | (left spaces trimmed)
• outer replace:|4|0|.|3|0|0| (spaces become '0')

so end 40.300.