SQL CHARINDEX | SQL Server CHARINDEX() Function

Summary: in this tutorial, you will learn learn SQL CHARINDEX() Function and how to use the SQL CHARINDEX() function to search for a substring in a string.

Definition and Usage

The SQL CHARINDEX() function searches for a substring in a string and it returns the position. Therefore, if the substring is not found, then the function returns 0.
Furthermore, The CHARINDEX() function searches for a substring inside a string starting from a specified location. Therefore, it returns the position of the substring found in the searched string, or zero if the substring is not found then the starting position returned is 1-based, not 0-based.

SQL CHARINDEX() Function Synatx

There are the following synatx:

Parameter Values

Parameter	Description
substring	Required. Substring is the substring to search for. Therefore, its length is limited to 8,000 characters.
string	Required. The string can be a literal string, expression, or column. It is a string to search.
start_location	Optional. The start_location is that location at which the search starts. Therefore, the start_location is an integer, big integer, or an expression that evaluates to a value of those data types. Furthermore, Here the start_location parameter is optional. If it is skipped, zero, or negative value, then the search starts at the beginning of the string.

Technical Details

Works in:	SQL Server (starting with 2008), Azure SQL Data Warehouse, Azure SQL Database, and Parallel Data Warehouse

SQL CHARINDEX() Function Examples

Let’s see and understand the few examples of using the CHARINDEX SQL function.
A) First: To perform a single search using CHARINDEX SQL Function
The given following example uses the CHARINDEX() function to perform a simple search of the string ‘Server’ in the 'SQL Server Management Studio'

SELECT 
    CHARINDEX('Server', 'SQL Server Management Studio') position;

Therefore, let’s execute the query to return the output.
Output:

position
-----------
5
(1 row affected)

B) Second: To perform a case-insensitive search using CHARINDEX() function
This statement shows a case-insensitive search for the string 'MANAGEMENT' in 'SQL Server Management Studio':

SELECT 
    CHARINDEX(
        'MANAGEMENT', 
        'SQL Server Management Studio'
    ) position;

Therefore, let’s execute the query to return the output.
Output:

position
-----------
12
(1 row affected)

C) Third: To perform a case-sensitive search using CHARINDEX SQL function
The following example shows a case-sensitive search for the string 'MANAGEMENT' in searched string SQL Server Management Studio.

SELECT 
    CHARINDEX(
        'MANAGEMENT', 
        'SQL Server Management Studio' 
        COLLATE Latin1_General_CS_AS
    ) position;

Output:

position
-----------
0
(1 row affected)

D) Fourth: To search for a nonexistent substring using CHARINDEX() function
The following example illustrates a search for the substring 'teacher' in the string 'School is Education Hub'

DECLARE @hub VARCHAR(100);  
SELECT @hub = 'School is Education Hub';  
SELECT CHARINDEX('teacher', @hub);

Therefore, let’s execute the query to return the output.
Output:

position
-----------
0
(1 row affected)

E) Fifth: To searching from a specific position using CHARINDEX() function
This example uses the start_location parameter to start the search for 'is' at the five and ten character of the string 'This is a my cousion sister':

SELECT 
    CHARINDEX('is','This is a my cousion sister',5) start_at_fifth,
    CHARINDEX('is','This is a my cousion sister',10) start_at_tenth;

Therefore, let’s execute the query to return the output.
Output:

start_at_fifth start_at_tenth
-------------- --------------
6              23
(1 row affected)

Conclusion

In this tutorial, you have learned SQL CHARINDEX Function and how to use them to search for a substring in a string starting from a specified location and return the position of the substring.