The SQL CHARINDEX function is a String Function that finds the index position of a given expression from existing records. The SQL CHARINDEX Function index position will start from 1, Not 0
SQL CHARINDEX Function Syntax
The syntax of the SQL CHARINDEX Function is
SELECT CHARINDEX (ExpressionToFind, ExpressionToSearch, Starting_Position) FROM [Source]
- ExpressionToFind: The 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. The SQL Server CHARINDEX function will search this expression within ExpressionToFind.
- Starting_Position: This argument is optional. If you specify the starting position, the CHARINDEX Function will start searching for this position. Otherwise, it starts searching from the beginning.
TIP: The CHARINDEX Function returns NULL if ExpressionToFind or expressionToSearch is NULL.
For this CHARINDEX Function in SQL Server example, we are going to use the below shown data
SQL CHARINDEX Function Example
The following CHARINDEX function query finds the index position of existing and non-existing items from the start position and specified position
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 'SQL CHARINDEX' --Searching from specific position – 20 SELECT CHARINDEX ('in', @ExpressionToSearch, 20) AS 'SQL CHARINDEX' --Searching for a non-existent word –0 SELECT CHARINDEX ('CHARINDEX', @ExpressionToSearch) AS 'SQL CHARINDEX'
OUTPUT
ANALYSIS
We used the CHARINDEX function to find the index position of ‘in’ from the variable @ExpressionToSearch.
--Searching for an existent word --37 SELECT CHARINDEX ('in', @ExpressionToSearch) AS 'SQL CHARINDEX'
In the next line, We used the starting position argument of the SQL CHARINDEX function. It means the String Function will start the search for ‘in’ from position 20, reduce the load.
--Searching from specific position – 20 SELECT CHARINDEX ('in', @ExpressionToSearch, 20) AS 'SQL CHARINDEX'
Next, we searched for CHARINDEX, which does not exist inside the variable. That’s why it is returning 0
--Searching for a non-existent word –0 SELECT CHARINDEX ('CHARINDEX', @ExpressionToSearch) AS 'SQL CHARINDEX'
CHARINDEX Function Example 2
The SQL Charindex function also allows you to search for the expressions inside the column values. In this SQL Server example, we are going to find the index position of an Empty Space inside the Department Name column
SELECT [FirstName] ,[LastName] ,[DepartmentName] ,CHARINDEX (' ', [DepartmentName]) AS [Space Position] FROM [Employe]
OUTPUT
NOTE: If you observe the second record, Although it has Empty space at multiple positions, CHARINDEX displayed the value 4. It is because the CHARINDEX function will display the index position of a first occurrence, and it does not care about the other occurrences.
CHARINDEX Function Example 3
In this example, We are going to find the index position of a @ symbol present in Email column using CHARINDEX function
SELECT [FirstName] ,[LastName] ,[DepartmentName] ,[Email] ,CHARINDEX ('@', [Email]) AS [@ Position] FROM [Employe]
OUTPUT
NOTE: We can’t use CHARINDEX on Image, Text and NText Data Types.