SQL CHARINDEX Function

The SQL CHARINDEX one of the SQL 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:

• ExpressionToFind: Please specify the valid Expression to find. CHARINDEX function will search for this expression, if it find 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 2014 with example. For this, We are going to use the below shown data

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 CHARINDEX function.

T-SQL CODE

OUTPUT

ANALYSIS

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

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

In the next line, We used the starting position argument inside the CHARINDEX function. It means function will start search for ‘in’ from position 20, reduce the load.

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

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

T-SQL CODE

OUTPUT

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

T-SQL CODE

OUTPUT

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

Thank You for Visiting Our Blog