String Functions - SQL Server 2008 -- CHARINDEX

CHARINDEX ( expression1, expression 2, [starting position]) :

This is one of the very important string functions in SQL Server. This function return the first position of a character or character string found within another character string. Here expression 1 is the string of character that would be searched in expression 2. Starting position is the index in expression 2 where CHARINDEX would start looking for expression 1 in expression 2.
The index of the first Character of expression2 is 1 .

Example:

expression 1 : you

expression 2 : Can you name the fastest animal on Earth. (MAX Length allowed in SS is 8000 characters)

Starting position : 0

In this case, we are trying to get the starting index of the expression1 if it exists in expression 2. SQL Server would start searching for the word "you" in expression 2 from left to right. If we did not specify the starting position, it is null , -ve or zero then the whole expression is searched starting from 1st position from left side.

In this particular case this function would return 5. Since in expression 2 the first character is at position 1, from there if you start counting, you would find the position of y (y of you) is 5th.





If expression 1 does not exist in expression 2 then in this case 0 is returned.

When to use it :
Most of the time when you need a specific substring in a string, you can use this function to get the starting/ending index of the substring.