How to use FINDSTRING() SSIS

FINDSTRING() works as the same as CHARINDEX() in TSQL

it returns the position of character or the string in side the string that we want to search

Syntax : FINDSTRING( “search string”,”searching term”,occurrence)

to better understand we will use a simple example in validating a email address, so for the email to be valid it needs to have at least one @ character

in the email address , if not its considered as not a valid address

As we learned how to use FINDSTRING() now we will use this functionality to get the initcap functionality in SSIS , Initcap is a cool feature in oracle when this function used,

The first letter of each word into uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric

This is how it is used in Oracle example

The following example capitalizes each word in the string:

\\***** PL\SQl****\\\
SELECT INITCAP(‘the soap’) “Capitals” FROM DUAL;

Capitals
———
The Soap
\\*** PL/SQl*****\\

Now we will achieve this in SSIS using FINDSTRING, TRIM, UPPER, and LOWER functions in derived columns

In the derived column we are using

(UPPER(SUBSTRING(TRIM(Name),1,1)) + TRIM(LOWER(SUBSTRING(TRIM(Name),2,FINDSTRING(TRIM(Name),” “,1) – 1 < 0 ? LEN(TRIM(Name)) : FINDSTRING(TRIM(Name)," ",1) – 1)))) + " " + (FINDSTRING(TRIM(Name)," ",1) – 1 < 0 ? " " : UPPER(SUBSTRING(TRIM(SUBSTRING(TRIM(Name),FINDSTRING(TRIM(Name)," ",1),LEN(TRIM(Name)))),1,1)) + TRIM(LOWER(SUBSTRING(TRIM(Name),FINDSTRING(TRIM(Name)," ",1) + 2,LEN(TRIM(Name))))))

The above expression looks big but it is very simple

In this we are taking the first letter of the word and making it upper case , if there is a empty in the string we are considering as a second word and then we are selecting the

first letter of the world to upper case and all other characters are mapped to lower case