CHARINDEX in SQL Server: Find String Positions Efficiently

The CHARINDEX function in SQL Server is a powerful tool for locating the starting position of a substring within a larger string. This function is essential for various string manipulation and data analysis tasks in SQL Server, Azure SQL Database, and other Microsoft data platforms. This article delves into the syntax, usage, and practical applications of CHARINDEX, providing a comprehensive guide for database professionals and developers.

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Understanding the Syntax of CHARINDEX

The CHARINDEX function uses a straightforward syntax, making it easy to implement in your SQL queries. Here’s the basic structure:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

Let’s break down each component:

expressionToFind: This is the substring you are searching for. It’s a character expression and has a limit of 8000 characters. This is also sometimes referred to as the needle in string searching terminology.
expressionToSearch: This is the string you will be searching within. It’s the larger character expression, often called the haystack.
start_location (Optional): This integer or bigint value specifies the position within expressionToSearch where the search should begin. If omitted, or if the value is zero or negative, the search starts at the beginning of expressionToSearch.

Transact-SQL syntax conventions

Return Types and Behavior

CHARINDEX returns different data types depending on the input:

bigint: If expressionToSearch is of type nvarchar(max), varbinary(max), or varchar(max).
int: Otherwise.

Key Behaviors to Note:

1-based Index: The position returned by CHARINDEX is 1-based, meaning the first character of the string is position 1, not 0, which is common in some programming languages.
Case Sensitivity: CHARINDEX is case-insensitive by default in most SQL Server collations. However, you can perform case-sensitive searches using the COLLATE clause.
NULL Handling: If either expressionToFind or expressionToSearch is NULL, CHARINDEX will return NULL.
Substring Not Found: If expressionToFind is not found within expressionToSearch, CHARINDEX returns 0.
Data Type Conversion: If one expression is Unicode (nchar, nvarchar) and the other is not, CHARINDEX will implicitly convert the non-Unicode expression to Unicode for comparison.
Limitations: CHARINDEX cannot be used with older data types like image, ntext, or text. For modern applications, it’s recommended to use varchar(max), nvarchar(max), or varbinary(max) instead.
Undefined Character: The character char(0) (0x0000) is undefined in Windows collations and cannot be used with CHARINDEX.

Practical Examples of CHARINDEX in SQL Server

Let’s explore various examples to understand how CHARINDEX works in practice.

Example 1: Basic String Search

This example demonstrates a simple search for the substring 'SQL' within the string 'Learning SQL Server is fun'.

SELECT CHARINDEX('SQL', 'Learning SQL Server is fun');
GO

Result:

-----------
10

The output 10 indicates that the substring 'SQL' starts at the 10th position in the searched string.

Example 2: Specifying a Starting Position

Here, we use the optional start_location parameter to begin the search for 'Server' from the 15th character.

SELECT CHARINDEX('Server', 'Learning SQL Server is fun', 15);
GO

Result:

-----------
0

The result 0 shows that 'Server' is not found in the string when starting the search from the 15th position. However, if we start from position 9:

SELECT CHARINDEX('Server', 'Learning SQL Server is fun', 9);
GO

Result:

-----------
13

This correctly identifies the starting position of 'Server' as 13 when the search begins from the 9th character.

Example 3: Case-Sensitive vs. Case-Insensitive Search

By default, CHARINDEX is case-insensitive.

SELECT CHARINDEX('server', 'Learning SQL Server is fun');
GO

Result:

-----------
13

To perform a case-sensitive search, use the COLLATE clause with a case-sensitive collation, such as Latin1_General_CS_AS.

SELECT CHARINDEX('server', 'Learning SQL Server is fun' COLLATE Latin1_General_CS_AS);
GO

Result:

-----------
0

In this case-sensitive search, 'server' (lowercase) is not found because the string contains 'Server' (uppercase ‘S’).

However, searching for 'Server' with case-sensitive collation will yield:

SELECT CHARINDEX('Server', 'Learning SQL Server is fun' COLLATE Latin1_General_CS_AS);
GO

Result:

-----------
13

Example 4: Using CHARINDEX with Variables

CHARINDEX can be effectively used with variables to make your queries more dynamic.

DECLARE @search_string VARCHAR(100) = 'SQL Server';
DECLARE @document VARCHAR(200) = 'This document explains features of SQL Server database.';

SELECT CHARINDEX(@search_string, @document) AS StartingPosition;
GO

Result:

StartingPosition
----------------
31

Example 5: CHARINDEX in WHERE Clause for Filtering

You can use CHARINDEX in the WHERE clause to filter results based on whether a substring exists in a column.

-- Assume you have a table 'Products' with a column 'ProductName'
SELECT ProductID, ProductName
FROM Products
WHERE CHARINDEX('Laptop', ProductName) > 0;

This query will retrieve all products where the ProductName column contains the substring 'Laptop'.

Example 6: Handling NULL Values

Demonstrating how CHARINDEX behaves with NULL inputs.

SELECT CHARINDEX('test', NULL);
GO

Result:

-----------
NULL

SELECT CHARINDEX(NULL, 'test string');
GO

Result:

-----------
NULL

As shown, if either input is NULL, the output is NULL.

Supplementary Characters and Collations

When working with Supplementary Characters (Surrogate Pairs) and SC collations, CHARINDEX correctly counts surrogate pairs as a single character. This is important for handling Unicode characters beyond the Basic Multilingual Plane (BMP). For more detailed information, refer to Collation and Unicode Support in SQL Server.

Conclusion

CHARINDEX is a fundamental string function in SQL Server, crucial for locating substrings within strings. Understanding its syntax, behavior, and options like start_location and COLLATE allows you to effectively use it for data manipulation, filtering, and analysis. By mastering CHARINDEX, you can write more efficient and powerful SQL queries to handle string-based data in your applications.