Wednesday, October 10, 2012

SSIS.FINDSTRING function

SQL Server Integration Services has provided many useful functions to the developers. FINDSTRING is one of the valuable string functions that come handy in many situations. Technically, this function is used to find occurrence of a specific character or string in a given series of string.

This function only returns integer values. It can return a 0 or any integer value greater than 0. This integer value is character index location in the string. It treats each character of the string as one index item. So, for example string “Hello”, it has five index items each storing one character. This function has following syntax.

FINDSTRING(StringValueToLookInto, StringValueToLookFor, Occurence)

This function has three parameters.

The first parameter is string value where this function will look into. It could be a column of a table or some expression or some hard coded string values. It is the string value which will be searched to find out specific string/character by FINDSTRING function.

The second parameter is string value that this function will look for. It could be some expression or some hard coded values. This is your specific string/character that you want to search.

The third parameter is numeric value where we specify how many occurrences till we want to search for.

An example will make it clear.

Let us say we want to search for string “hello” in string “You should say hello to people when you meet”. Then we should write

FINDSTRING(“You should say hello to people when you meet”, “hello”, 1)

This will return value 16, which is the place from where the string “hello” starts.

When this function returns 0, it means that the function did not find any character/string that matches. Following example will return 0 because the string value “bye” is not found.

Let us take another example. For demonstration, let us say we have following data in excel spreadsheet. We want to figure out which company sells DVD from the list.

I created one SSIS package and added a Data Flow Task. I added a Derived Column where we will use and evaluate FINDSTRING function.

I added a column FindProducts and used FINDSTRING function in the expression. We know that FINDSTRING function returns a numeric value so we want to evaluate does it return any value that is greater than 0, if yes, we want to display “Company Sells DVD”. If the FINDSTRING () function returns 0, we want to display “Company Donot sell DVD”.

FINDSTRING(Produts,"DVD",1) > 0 ? "Company Sells DVD" : "Company Donot sell DVD"

Running the package returns following values in FindProducts column. There were three companies who were selling DVD and one company that was not selling. So the result was as expected.

The point to remember about FINDSTRING function is that it only takes string values as its first and second parameter i.e. it only searches inside a string. So if we have a situation where we want to search in a date or integer or other data type columns, we need to explicitly convert that to String.

Following example will show, how we can use FINDSTRING function with a date. Let say we want to find out what day is today if today is Saturday or Sunday we want to display “Weekend day WOW” else we want to display Monday to Friday as “Work Day”.

(FINDSTRING((DT_STR,20,1252)DATEPART("dw",GETDATE()),"1",1) == 1 || FINDSTRING((DT_STR,20,1252)DATEPART("dw",GETDATE()),"7",1) == 1) ? "Weekend day WOW" : "Work Day"

Thanks for reading till this point.



Related Article:
Labels: ,

Popular Posts

Real Time Web Analytics