Tutorial Gateway

SQL CHARINDEX Function

by

The SQL CHARINDEX one of the String Function, which is used to find the index position of a specified expression from existing records.

TIP: The index position of the CHARINDEX Function will start from 1, Not 0.

SQL CHARINDEX Function Syntax

The basic syntax of the SQL CHARINDEX Function In SQL Server is as shown below:

SELECT CHARINDEX (ExpressionToFind, ExpressionToSearch, Starting_Position)
FROM [Source]
  • ExpressionToFind: Please specify the valid Expression to find. The CHARINDEX function will search for this expression, if it finds the expression then it will return the index position otherwise, it will return 0.
  • ExpressionToSearch: Please specify the valid Expression on which to want to perform search operation. The CHARINDEX function will search this expression for ExpressionToFind.
  • Starting_Position: This argument is optional. If you specify the starting position then CHARINDEX Function will start searching from this position otherwise, it will start searching from the beginning.

TIP: The CHARINDEX Function will return NULL if ExpressionToFind or expressionToSearch is NULL.

In this article, we will show you, How to write CHARINDEX Function in SQL Server with an example. For this, We are going to use the below shown data

SQL Employe Source

 

SQL CHARINDEX Function Example 1

CHARINDEX Function is used to search for index position of a specific expression from the original string. The following query will show multiple ways to use the CHARINDEX function.

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

ANALYSIS

Below lines of code is used to declare a string variable and assigning the string data.

DECLARE @ExpressionToSearch varchar(100)
SET @ExpressionToSearch = 'Learn SQL Server 2014 with Examples in Tutorial Gateway'

In the below statement, We used CHARINDEX function to find the index position of ‘in’ from the variable @ExpressionToSearch. We also assigned a new name to that result as ‘SQL CHARINDEX’ using ALIAS Column.

--Searching for an existent word --37
SELECT CHARINDEX ('in', @ExpressionToSearch) AS 'SQL CHARINDEX'

In the next line, We used the starting position argument inside the CHARINDEX function. It means the 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'

In the next line, We searched for CHARINDEX, which is not existed inside the variable. That’s why it is returning 0

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

SQL CHARINDEX Function Example 2

The CHARINDEX function also allows you to search for the expressions inside the column values. In this 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

SQL CHARINDEX FUNCTION 2

NOTE: If you observe the second record, Although it has Empty space at multiple position CHARINDEX displayed the value 4. This is because, CHARINDEX will display the index position of a first occurrence and it does not care after that.

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

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

Thank You for Visiting Our Blog

Placed Under: SQL