Solved

Using Replace function in Derived Column -- SSIS

Posted on 2013-06-10
7
7,437 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 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