SQL Server's CHARINDEX Function

In my previous posts, I explained CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters, SQL query result as XML and some other articles related to SQL Server.

Here, I am going to explain CHARINDEX function of SQL Server.

CHARINDEX() Function

This function is used to search the specified word or substring in over all string input value. It returns the starting index position of the matching input value. If there is no match found then it returns 0(zero).
 

Syntax- CHARINDEX(StringToFind, StringToSearch [, start_location])
Arguments- 

• StringToFind : An expression containing the sequence of character to be found. 
• StringToSearch : An expression , usually a column searched for specified sequence.
• start_location : It is the charecter position to start searching of StringToFind from StringToSearch.

Return Type- int

Example-

Declare @inputString as varchar(100)

set @inputString='Dot Net World, A blog for Dot Net Programmers'

Select CHARINDEX('Blog',@inputString)

--Output 18

Declare @inputString as varchar(100)

set @inputString='Dot Net World, A blog for Dot Net Programmers'

Select CHARINDEX('Blog',@inputString,10)--search will start from 10th position of the string

--Output 18