expression1 is a sequence of characters to be found from expression2. It is of short character data type.
expression2 is a string from which expression1 is searched. It is of character string data type.
start_location is a position to start searching for expression1 in expression2. It is an optional field. If you don't specify or specify 0 or negative number the search starts from beginning of expression2.
It returns an integer value.
Examples of CHARINDEX Function :
Example 1 : Use of CHARINDEX function in select clause
SELECT CHARINDEX('ax','Syntax-Example-Syntax',0)
OR
SELECT CHARINDEX('ax','Syntax-Example-Syntax')
Output
5
Above example returns position of starting index for characters 'ax' in specified string. It starts searching from beginning of the string. As it starts searching from beginning of the string, it returns position of 1st occurrence of 'ax' from expression2.
SELECT CHARINDEX('ax','Syntax-Example-Syntax',6)
Output
20
Above example returns position of starting index for characters 'ax' in specified string, this time it starts searching after starting 6 characters. As it starts searching after 6 characters, it returns position of 2nd occurrence of 'ax' from expression2.
Example 2 : Use of CHARINDEX function to display field value of table
SELECT ContactName, CHARINDEX('an',ContactName) AS 'Index'
FROM Customers
Output
ContactName Index
Maria Anders 7
Ana Trujillo 1
Antonio Moreno 1
Thomas Hardy 0
Christina Berglund 0
Hanna Moos 2
Above example displays starting position of 'an' in all customer names from customers table.
Example 3 : Use of CHARINDEX function in where clause
SELECT ContactName
FROM Customers
WHERE CHARINDEX('an',ContactName) > 0
Output
Maria Anders
Ana Trujillo
Antonio Moreno
Hanna Moos
Above example displays all customer names having substring 'an' in customer name of customers table.