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