SQL CHARINDEX FUNCTION 1

SQL CHARINDEX Function

by

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 Employe Source

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'
SQL CHARINDEX FUNCTION 1

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]

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.

SQL CHARINDEX FUNCTION 2

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]
SQL CHARINDEX FUNCTION 3

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