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 SQL 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. The SQL CHARINDEX will search this expression within ExpressionToFind.
  • Starting Position: 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 CHARINDEX Function Example

The following SQL CHARINDEX function query finds the index position of existing and non-existing items from the start position and specified position. First, We are finding the position of ‘in’ from the variable @ExpressionToSearch.

In the next line, We used the starting argument of the SQL CHARINDEX function. It means the String method will start the search for ‘in’ from 20, reduce the load on 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

CHARINDEX Function Example 2

It also allows you to search for substring 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 it does not care about the other occurrences.

SQL CHARINDEX FUNCTION 2

In this example, We are going to find the index position of a @ symbol present in Email address column using the string CHARINDEX in SQL.

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

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