Solved

Using Replace function in Derived Column -- SSIS

Posted on 2013-06-10
7
7,727 Views
Last Modified: 2016-02-11
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!
0
Comment
Question by:ravichand-sql
  • 5
7 Comments
 

Author Comment

by:ravichand-sql
ID: 39235514
Never mind! The former solution in my description works.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39237388
Convert that Column to Numeric datatype. It will automatically remove leading zero. and then convert it to string (varchar/nvarchar)
0
 

Author Comment

by:ravichand-sql
ID: 39237736
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 39240867
Hi,
Are you expecting an exact number of leading zeros, or it varies?  one zero, 2 zeros ...etc... ?
0
 

Author Comment

by:ravichand-sql
ID: 39240954
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
 

Accepted Solution

by:
ravichand-sql earned 0 total points
ID: 39271611
This will work,

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

Author Closing Comment

by:ravichand-sql
ID: 39286376
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSAS Store Forecasting data in the cube 1 20
SQL Server Configuration Manager WMI Error 11 21
Are triggers slow? 7 15
sql2016-WIn10: standard,for SQL servc-account.. 51 31
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question