Using Replace function in Derived Column -- SSIS

Hello Experts,

I am trying to remove leading zeroes off of a source column when loading the data to a sql server table. I have to handle this in SSIS and hence using derived column transformation.

Initially I tried this:

(DT_STR,30,1252)REPLACE(LTRIM(REPLACE([Column 1],"0","")),"","0")

But, the above expression is removing all the zeroes from the record no matter where the zero is.

For Example: If I have "0012052400", it is transforming it into "12524"

I tried doing this:

(DT_STR,30,1252)(DT_I8)[Column 1]

This is giving me correct results, But if I get any text in that column then my package will fail. As the Column 1 has text and it cannot be converted to INT.

Please help me with a work around for this.

Thanks in advance!
ravichand-sqlAsked:
Who is Participating?
 
ravichand-sqlConnect With a Mentor Author Commented:
This will work,

(DT_STR,30,1252)REPLACE(LTRIM(REPLACE([Column 1],"0","")),"","0")
0
 
ravichand-sqlAuthor Commented:
Never mind! The former solution in my description works.
0
 
Alpesh PatelAssistant ConsultantCommented:
Convert that Column to Numeric datatype. It will automatically remove leading zero. and then convert it to string (varchar/nvarchar)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ravichand-sqlAuthor Commented:
PatelAlpesh,

Thanks for your reply.

Process will fail if I have any character data instead of numeric in the source column.

This will work:

(DT_STR,30,1252)REPLACE(LTRIM(REPLACE([Column 1],"0","")),"","0")


Thank you.
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Hi,
Are you expecting an exact number of leading zeros, or it varies?  one zero, 2 zeros ...etc... ?
0
 
ravichand-sqlAuthor Commented:
Huslayer,

I got the fix for this question and mentioned it in my above comments. Thank you very much for your time. I appreciate it!!

Thanks!!!
0
 
ravichand-sqlAuthor Commented:
This solution did not work for me intially but this was not the issue causing problem. I had that other issue resolved and this code worked just fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.