CHARINDEX() function in SQL

In this tutorial, we will learn about CHARINDEX() function in SQL. For learning this further, we have to be familiar with SQL.
SQL stands for Structured Query Language. It is used to create and access data from the database. For accessing the data from the database in SQL, we have to write a query. The query is basically a SELECT statement that is used to access the data from the database.

More About SQL

creating database in SQL
Writing Query in SQL

SQL function CHARINDEX() is used to find the index value of the specified character or substring in the specified string. And if the character is not found then it will return 0.

SQL CHARINDEX() function Syntax

CHARINDEX(substring/character, string , begin)

substring/character: Here substring/character is that value whose position you wanna know.

string: String is, in which you want to know the position of your specified substring/character.

begin: It is a value from which you want to start to search your specified substring/character. This is optional. By default, its value is one.

SQL CHARINDEX Function Examples

Let’s understand with the help of some examples :

Example 1:

SELECT CHARINDEX('c','country') As position

Output :

position
1

Example 2:

SELECT CHARINDEX('t','country') As position

Output :

position
5

Example 3:

SELECT CHARINDEX('h','country') As position

Output :

position
0

Example 4:

SELECT CHARINDEX(' are ','We are Indians') As position

Note: Here we take space at both sides of ‘are’. That means it will search space first.

Output :

position
3

Example 5:

SELECT CHARINDEX('are','We are Indians') As position

Note: Here we take “are” without spaces.

Output :

position
4

Example 6:

SELECT CHARINDEX('c','country',3) As position

Note: It will start searching for the position of character ‘c’ from the 3 positions.

Output :

position
0

Example 7:

SELECT CHARINDEX('c','councctry',3) As position

Output :

position
5

Note: it will only give the position of the first occurrence of the specified character.