FINDSTRING (SSIS Expression)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Returns the location of the specified occurrence of a string within a character expression. The return result is the one-based index of the occurrence. The string parameter must evaluate to a character expression, and the occurrence parameter must evaluate to an integer. If the string is not found, the return value is 0. If the string occurs fewer times than the occurrence argument specifies, the return value is 0.
FINDSTRING works only with the DT_WSTR data type. character_expression and searchstring arguments that are string literals or data columns with the DT_STR data type are implicitly cast to the DT_WSTR data type before FINDSTRING performs its operation. Other data types must be explicitly cast to the DT_WSTR data type. For more information, see Integration Services Data Types and Cast (SSIS Expression).
FINDSTRING returns null if either character_expression or searchstring are null.
Use a value of 1 in the occurrence argument to get the index of the first occurrence, 2 for the second occurrence and so forth.
The occurrence must be an integer with a value greater than 0.
This example uses a string literal. It returns the value 11.
FINDSTRING("New York, NY, NY", "NY", 1)
This example uses a string literal. Because the string "NY" occurs only two times, the return result is 0.
FINDSTRING("New York, NY, NY", "NY", 3)
This example uses the Name column. It returns the location of the value n in the Name column. The return result varies depending on the value in Name. If Name contains Anderson, the function returns 8.
This example uses the Name and Size columns. It returns the location of the leftmost character of the Size value in the Name column. The return result varies depending on column values. If Name contains Mountain,500Red,42 and Size contains 42, the return result is 17.