SSIS FINDSTRING Function

The SSIS FINDSTRING is a string function to find and return the location (index position) of the specified occurrence of a substring within a string or character expression. This article explains how to use the SSIS FINDSTRING function with an example and the syntax for finding the location of the substring or character is as shown below.

FINDSTRING(<<Character_expression>>, <<String>>, <<Occurrence>>)
  1. Character_expression: Original String or the Column name where the search will happen.
  2. String: A character or the substring to search within the Character_expression.
  3. Occurrence: It is an integer value that decides from which occurrence the FINDSTRING function has to read the location.

To demonstrate the SSIS FINDSTRING 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 FINDSTRING function

For this FINDSTRING example, 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 FINDSTRING 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 FINDSTRING function to the row cell under the Expression and replace the Character_expression with the string column name, String with substring, or character and occurrence with an integer. Either you can manually delete the complete <<Character_expression>> expression or drag and drop the column will automatically replace it.

FINDSTRING( [Name] , " ", 1 )

FINDSTRING( [Name] , " ", 2 )

FINDSTRING( [Phone] , "-", 1 )
SSIS FINDSTRING Function Expression

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 FINDSTRING function package to see the locations of the space within the name and – within the Phone fields.

  • Name1 – It shows the location of the first occurrence of the space within the Name field.
  • Name2 – It shows the location of the second occurrence of the space within the Name field.
  • Phone1 – It shows the location of the first occurrence of the “-” within the Phone field. If I changed the second argument to 2, the FINDSTRING function returns 8 (instead of 4).
SSIS FINDSTRING Function package output