SQL Server CHARINDEX() Function

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

SQL Server CHARINDEX() function overview

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

Here’s the syntax of the CHARINDEX() function:

CHARINDEX(substring, string [, start_location])Code language: SQL (Structured Query Language) (sql)

In this syntax:

substring is the substring to search for. Its length is limited to 8,000 characters.
string can be a literal string, expression, or column. It is a string to search.
start_location is the location at which the search starts. The start_location is an integer, big integer, or an expression that evaluates to a value of those data types.

The start_location parameter is optional. If it is skipped, zero, or negative value, the search starts at the beginning of the string.

Note that the CHARINDEX() function can perform both case-sensitive and case-insensitive searches based on the specified collation.

SQL Server CHARINDEX() function examples

Let’s take some examples of using the CHARINDEX() function.

1) Using SQL Server CHARINDEX() to perform a single search

The following example uses the CHARINDEX() function to perform a simple search of the string 'SQL' in the 'SQL Server CHARINDEX'

SELECT 
    CHARINDEX('SQL', 'SQL Server CHARINDEX') position;Code language: SQL (Structured Query Language) (sql)

Here is the output:

position
-----------
1

(1 row affected)

2) Using the CHARINDEX() function to perform a case-insensitive search

This statement shows how to use the CHARINDEX() function to perform a case-insensitive search for the string 'SERVER' in 'SQL Server CHARINDEX':

SELECT 
    CHARINDEX(
        'SERVER', 
        'SQL Server CHARINDEX'
    ) position;Code language: SQL (Structured Query Language) (sql)

Output:

position
-----------
5

(1 row affected)

3) Using the CHARINDEX() function to perform a case-sensitive search

The following example uses the CHARINDEX() function to perform a case-sensitive search for the string 'SERVER' in the string SQL Server CHARINDEX.

SELECT 
    CHARINDEX(
        'SERVER', 
        'SQL Server CHARINDEX' 
        COLLATE Latin1_General_CS_AS
    ) position;
Code language: SQL (Structured Query Language) (sql)

Output:

position
-----------
0

(1 row affected)

4) Using the CHARINDEX() function to search for a nonexistent substring

The following example uses the CHARINDEX() function to search for the substring 'needle' in the string 'This is a haystack':

DECLARE @haystack VARCHAR(100);  
SELECT @haystack = 'This is a haystack';  
SELECT CHARINDEX('needle', @haystack);  Code language: SQL (Structured Query Language) (sql)

Output:

position
-----------
0

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

5) Using the CHARINDEX() function to search from a specific position

This example uses the start_location parameter to start the search for 'is' at the fifth and tenth character of the string 'This is a my sister':

SELECT 
    CHARINDEX('is','This is a my sister',5) start_at_fifth,
    CHARINDEX('is','This is a my sister',10) start_at_tenth;Code language: SQL (Structured Query Language) (sql)

Here is the output:

start_at_fifth start_at_tenth
-------------- --------------
6              15

(1 row affected)

Summary

Use the SQL Server CHARINDEX() function to search for a substring in a string starting from a specified location and return the position of the substring.

Was this tutorial helpful?