Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a source table where one of column has data like:

Column
SD
SD
..
..
JK
Jk
..
..

I want to load this data into staging in format of:

Column
S&D
S&D
..
..
J&K
J&k
..
..

Meaning I want to insert '&' character wherever we have data like SD and JK, while loading to staging.

I need to convert it for SSIS. So I want to give here SSIS formula to achieve this.

How can we achieve this?

share|improve this question
    
will the data only be SD and JK ? You can use TSQL script task in SSIS and use case when SD then 'S&D' (if you only have SD and JK). Please clarify ! –  Kin Aug 14 '13 at 13:32
    
Thanks @Kin It has worked as you suggested using CASE... Thanks you....... –  ramu Aug 14 '13 at 13:55
1  
ramu, please don't vandalize your posts. –  Mat Aug 22 '13 at 9:33
add comment

3 Answers

Assuming your source column is called col, I would add a Derived Column Transformation. Use the substring and right function to split the column apart and insert the ampersand in the middle.

SUBSTRING(col,1,1) + "&" + RIGHT(col,1)
share|improve this answer
add comment

You can use TSQL script task in SSIS and use case when SD then 'S&D' (if you only have SD and JK).

Note: There may be better ways for complex scenarios, but in this scenario, using TSQL does the job.

share|improve this answer
add comment

There are a few ways to accomplish that. You can use a T-SQL Script Task or Derived Column Transformation as others have mentioned already.

In the end you need to use some kind of string manipulation function to get that additional character in there. The simplest I have come across so far is STUFF:

SQL Fiddle

Query 1:

SELECT X,
-----------------------------
    STUFF(X,2,0,'&') AS [X/&]
-----------------------------
 FROM (
    VALUES('SD'),('JK')
 )X(X);

Results:

|  X | X/& |
------------
| SD | S&D |
| JK | J&K |

STUFF takes a start position and a length (can be 0) and replaces that substring with another string: http://technet.microsoft.com/en-us/library/ms188043.aspx

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.