CODEWITHSUNDEEP
ssis
user2588812
user2588812

Reputation: 67

SSIS transformation Error

I have rows with JR or SR in there name column I want them to be at the end I could remove the special characters but confused in this step

 NUM           VEH              NAME                 NAME_Clean
 017            1       CLARK, jr WILLIAM            CLARK WILLIAM JR
 037            2       DESORMEAUX, JR. MICHELLE     DESORMEAUX MICHELLE JR
 043            1       FALCON, JENNIFER,jr          FALCON JENNIFER JR
 073            2       WINTERS, ALLEN               WINTERS ALLEN

Upvotes: 0

Views: 217

Answers (2)

criticalfix
criticalfix

Reputation: 2870

You can use a Derived Column Transformation with an SSIS Expression to replace an embedded " JR " with a trailing " JR":

FINDSTRING(NAME, " JR ") > 0 ? REPLACE(NAME, " JR ", "") + " JR" : NAME

If you would like to do this for several different strings, you can nest the expressions, or use multiple transformations one after the other.

For more information, see SSIS Functions, REPLACE, FINDSTRING, the Concatenate operator, and the Conditional operator (?:).

Upvotes: 1

Catalin
Catalin

Reputation: 561

use a Derived Column transformation and create a new derived column based on NAME column with the following:

(FINDSTRING([NAME],"jr",1)==1||FINDSTRING([NAME],"JR",1)==1)?REPLACE(REPLACE(REPLACE(REPLACE([NAME],",",""),".",""),"JR",""),"jr","")+" JR":REPLACE(REPLACE([NAME],",",""),".","")

note that the first expression FINDSTRING works only with DT_WSTR data type

good luck :)

Upvotes: 2

Related Questions