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?

  • 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 '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
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, privacy policy and cookie policy

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