Use of CHARINDEX Function in SQL Server ?

CHARINDEX function is used to find the position of the expression in a given character expression.

Syntax:

CHARINDEX (ExpressionToFind,CharacterExpression,[Starting_Position])

Example:

SELECT CHARINDEX('SQL','JK SQL SERVER BLOG SPOT JK SQL SERVER')

Output:

4

Explanation:

The above example will return the position of the 'SQL' in the character expression.The expression 'SQL' is found in the 4th position of the character expression.

So it returns the output value as 4.

Starting_Position in CHARINDEX function is optional.

Let's see another example which uses the optional parameter.

If you want to return the position of the 'SQL' after the 4th position the you can use the following syntax.

SELECT CHARINDEX('SQL','JK SQL SERVER BLOG SPOT JK SQL SERVER',5)

Now the output value will be 28.

Now the function will skip the value from 1st to 4th character , then it will search for the next occurrence of the word in the character expression.

And finally it found the value at the 28th position in the character expression.So it returns the value as 28.

Note : If you want to find the position of the character from the starting position itself then no need to use the starting_position parameter if not so then you can go head with 3 parameters.