SQL CHARINDEX FUNCTION 1

SQL CHARINDEX Function

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 Function Syntax

The syntax of the CHARINDEX Function is

SELECT CHARINDEX(ExpressionToFind, ExpressionToSearch, Starting_Position)
FROM [Source]
  • ExpressionToFind: This 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 the position.

It returns NULL if ExpressionToFind or expressionToSearch is NULL. For this SQL Server CHARINDEX Function example, we are going to use the below-shown data.

Employe Source

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.

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 'SQLCHARINDEX' 

--Searching from specific position – 20
SELECT CHARINDEX('in', @ExpressionToSearch, 20) AS 'SQLCHARINDEX' 

--Searching for a non-existent word –0
SELECT CHARINDEX('CHARINDEX', @ExpressionToSearch) AS 'SQLCHARINDEX'
SQL CHARINDEX FUNCTION 1

Example 2

It also allows you to search for substrings in a string within 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]

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.

Finding the Empty Space in a Column 2

In this example, we will find the index position of a @ symbol present in the Email address column using the string CHARINDEX.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,CHARINDEX('@', [Email]) AS [@ Position]
 FROM [Employe]
SQL CHARINDEX FUNCTION to find the @ position in Email 3

NOTE: We can’t use this function on Image, Text, and NText Data Types.