0

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?

3
  • 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 Shah
    Aug 14, 2013 at 13:32
  • Thanks @Kin It has worked as you suggested using CASE... Thanks you.......
    – ramu
    Aug 14, 2013 at 13:55
  • 1
    ramu, please don't vandalize your posts.
    – Mat
    Aug 22, 2013 at 9:33

3 Answers 3

2

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)
1

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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