SSIS String Replace

In SSIS, there is a REPLACE function that will search for the give expression within the character expression and if it finds then replace it with another string. It is helpful to replace the bulk product names, colors, etc. This article explains how to use the REPLACE function with an example and the syntax to replace the substring with a completely new string is as shown below.

REPLACE(<<character_expression>>, <<search_expression>>, <<replace_expression>>)
  • character_expression: Original String or the Column name where the search will happen.
  • search_expression: A character or the substring to search within the character_expression.
  • replace_expression: A character or the substring to replace in place of search_expression.

To demonstrate the SSIS string REPLACE function, we use the Employee table below, which has 15 records. Please refer to the Union All Transformation, Derived Column Transformation, Built-in Functions, and SQL Server articles in SSIS.

Source Table

SSIS String Replace

For this REPLACE function, let me add a Data Flow Task and double-click on it to enter the Data Flow region. Next, add the OLE DB Source to read data from the above SQL database table. Please join the OLE DB Source to Derived Column Transformation because it helps to write REPLACE function expressions and create new columns.

Data Flow to add Derived Column

Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS REPLACE function to the row cell under the Expression and replace the Character_expression with the string column name, search_expression with character or substring, and replace_expression with another substring. Either you can manually delete the complete expression or drag and drop the column will automatically replace it.

The below expression will replace the empty spaces within the Name column with * symbols.

REPLACE( [Name], " ", "*" )
SSIS String Replace Expression

The below expression will replace the “-” symbol within the Phone column with spaces.

REPLACE( [Phone], "-", " " )

Click OK to close the Editor window.

Drag and drop the Union All Transformation and connect the Derived Column to it. Next, right-click on the connection node and choose the Enable Data Viewer. Please run the SSIS string REPLACE function package to see the replaced names and phone numbers.

SSIS String Replace output