SQL CHARINDEX() | LOCATE() | INSTR() Function


The SQL CHARINDEX() | LOCATE() | INSTR() is a function and returns the index position of the first occurrence of substring of a given input string or text.

The SQL CHARINDEX() use to find the numeric starting position of a search string inside another string.

The SQL CHARINDEX() function returns "0" if given substring does not exist in the input string.

The SQL CHARINDEX() function is supports or work with character and numeric based columns.

It can be used in any valid SQL SELECT statement as well in SQL where clause.


SQL CHARINDEX() | LOCATE() | INSTR() Syntax

The basic syntax to retrieve index posiotion of a substring from a given input string

For Sql Server


SELECT CHARINDEX(sub_string1, string1[, start_location]);

For MySql


SELECT LOCATE(sub_string1, string1[, start_location]);

In the above both syntax has same arguments in their function and below detail of that arguments.

Parameter EmpName Description
sub_string1 Required. The string to find the index position of a sequence of characters.
string1 Required. The sequence of characters or string that will be searched for to index position of substring1 in string1 or column_EmpName1.
start_location Optional. Instructs the function to ignore a given number of characters at the beginning of the string to be searched.

For MS Access


SELECT INSTR([start_location,] string1, sub_string1);

Here, parameters are in reverse order, but meaning is same.


Search Keys

  • sql charindex function
  • charindex
  • sql server charindex
  • mssql charindex
  • charindex example
  • oracle charindex
  • sql charindex example
  • charindex sql server example
  • sql substring charindex
  • sql indexof

SQL CHARINDEX() | LOCATE() | INSTR() Example - Using Expression Or Formula

The following SQL SELECT statement find the index position of sequence of characters or a string within a string.

For SQL Server


SELECT 
CHARINDEX('a', 'Sql Database') AS 'Find Index Of a',
CHARINDEX('a', 'Sql Database', 7) AS 'Skip 7 characters and Find Index Of a';

For MySql


SELECT 
LOCATE('a', 'Sql Database') AS 'Find Index Of a',
LOCATE('a', 'Sql Database', 7) AS 'Skip 7 characters and Find Index Of a';

For MS Access


SELECT 
INSTR('Sql Database', 'a') AS 'Find Index Of a',
INSTR(7, 'Sql Database', 'a') AS 'Skip 7 characters and Find Index Of a';

The result of above query is:

Find Index Of 'a' Skip 7 characters and Find Index Of 'a'
6 8

Sample Database Table - Employee

ID EmpEmpName Designation Dept JoinYear Salary
1 Harish Karthik Manager MS Access 2012 7040
2 Devi Mai Mobile Database ASP.Net 2012 20480
3 Hanumanthan Computer Science MySQL 2012 12290.3
4 Azaghu Varshith SQL Database PHP 2013 9350.6
5 Ranjani Mai Sql Team Adminstrator PHP 2014 10610.6
6 Sakunthala Cloud Database PHP 2015 2000
7 Nirmala Database Query Engine C#.Net 2014 14810.1
8 Ramanathan Big Data PHP 2014 20690.6
9 Hari Krishnan SQL Database ASP.Net 2012 7250.1
10 Pandurengan Administrator MS Access 2015 8720
11 Rishi Keshan Web Mining Oracle 2014 13970.4
12 Keshavan Database Security MS Access 2012 19640

SQL CHARINDEX() | LOCATE() | INSTR() Example - With Table Column

The following SQL statement CHARINDEX the "EmpName" and "Designation" column from the "Employee" table:

For SQL SERVER


SELECT 
EmpName, CHARINDEX('i', EmpName) As 'Index Of i in EmpName', 
Designation, CHARINDEX('data', Designation) As 'Index Position Of data in Designation' 
FROM Employee;

For MySql


SELECT 
EmpName, LOCATE('i', EmpName) As 'Index Of i in EmpName', 
Designation, LOCATE('data', Designation) As 'Index Position Of data in Designation' 
FROM Employee;

For MS Access


SELECT 
EmpName, INSTR('i', EmpName) As 'Index Of i in EmpName', 
Designation, INSTR('data', Designation) As 'Index Position Of data in Designation' 
FROM Employee;

The result of above query is:

EmpName Index Of "i" in EmpName Designation Index Of "data" in Designation
Harish Karthik 4 Manager 0
Devi Mai 4 Mobile Database 8
Hanumanthan 0 Computer Science 0
Azaghu Varshith 13 SQL Database 5
Ranjani Mai 7 Sql Team Adminstrator 0
Sakunthala 0 Cloud Database 7
Nirmala 2 Database Query Engine 1
Ramanathan 0 Big Data 5
Hari Krishnan 4 SQL Database 5
Pandurengan 0 Administrator 0
Rishi Keshan 2 Web Mining 0
Keshavan 0 Database Security 1