SSIS transformation Error

Question

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

Answer 1

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 (?:).

Answer 2

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