The SQL CHARINDEX function is a String method that finds the index position of a given expression from existing records. The index position will start from 1, Not 0.
SQL CHARINDEX Syntax
The syntax of the CHARINDEX Function is
SELECT CHARINDEX(ExpressionToFind, ExpressionToSearch, Starting_Position) FROM [Source]
Arguments
- ExpressionToFind: This CHARINDEX function will search for this expression. If it finds the expression, it returns the index position. Otherwise, it will return 0.
- ExpressionToSearch: Expression on which you want to perform the search operation. It will search this expression within ExpressionToFind.
- Starting: It is an optional argument. If you specify the start location, it will start searching for this character position. Otherwise, it starts searching from the beginning and returns to its position.
Return Value: It returns NULL if ExpressionToFind or expressionToSearch is NULL. For this CHARINDEX Function example, we will use the below-shown data.
SQL Server CHARINDEX Function Example
The following CHARINDEX function query finds the index position of existing and non-existing items from the start and specified positions. First, We are finding the position of ‘in’ from the variable @ExpressionToSearch.
Search from a specific position: In the next line, we used the starting argument of the CHARINDEX function. It means the String method will start the search for ‘in’ from 20, reducing the load on the server. The query execution will become faster.
Next, we searched for the following word, which does not exist inside the variable. That’s why it is returning 0.
DECLARE @ExpressionToSearch varchar(100)
SET @ExpressionToSearch = 'Learn SQL Server 2014 with Examples in Tutorial Gateway'
--Searching for a existent word --37
SELECT CHARINDEX('in', @ExpressionToSearch) AS 'res'
--Searching from specific position – 20
SELECT CHARINDEX('in', @ExpressionToSearch, 20) AS 'res'
--Searching for a non-existent word –0
SELECT CHARINDEX('CHARINDEX', @ExpressionToSearch) AS 'res'

Use SQL CHARINDEX() to search for a non-existent expression
In the query below, we declare a @description variable to store the product description. Next, we used CHARINDEX() to find the index position of a non-existent substring. If it does not find the substring in the main string, CHARINDEX() returns 0.
DECLARE @description VARCHAR(MAX);
SET @description = 'Aluminum alloy cups and a hollow axle.'
SELECT CHARINDEX('Cycle', @description)
0
Use CHARINDEX() to perform a case-sensitive search
By default, CHARINDEX() performs a case-insensitive search operation to find the index position of the substring.
SELECT CHARINDEX('Bike', 'BMW BIKE')
5
However, if we use COLLATE, we can make CHARINDEX() perform a case-sensitive search operation.
SELECT CHARINDEX('Bike', 'BMW BIKE' COLLATE Latin1_General_CS_AS)
0
To make it explicitly case-insensitive, we can use the third argument of CHARINDEX() as shown below.
SELECT CHARINDEX('Bike', 'BMW BIKE' COLLATE Latin1_General_CI_AS)
5
Use SQL CHARINDEX() function on table columns
The CHARINDEX function also allows you to search for substrings in a string within the column values. In this SQL Server tutorial example, we will find the index position of an Empty Space inside the Department Name column.
SELECT [FirstName]
,[LastName]
,[DepartmentName]
,CHARINDEX(' ', [DepartmentName]) AS [Space Position]
FROM [Employe]
If you observe the second record, Although it has Empty space at multiple locations, it displayed the value 4. It is because CHARINDEX will display the location of a first occurrence and does not care about the other occurrences.

How to find Email @ Position using CHARINDEX?
In this example, we will find the index position of the @ symbol present in the Email address column using the string CHARINDEX. Please refer to the SQL PATINDEX and SQL SUBSTRING methods from the list of available SQL Server String functions.
NOTE: We can’t use the CHARINDEX() function on Image, Text, and NText Data Types.
SELECT [FirstName]
,[LastName]
,[DepartmentName]
,[Email]
,CHARINDEX('@', [Email]) AS [@ Position]
FROM [Employe]
