CHARINDEX() function SQL Server

What is SQL Server CHARINDEX() Function?

The CHARINDEX() function in SQL Server is a valuable tool used to search for a specific substring within a given string and subsequently retrieve its position. In cases where the desired substring cannot be located within the string, the CHARINDEX() function will yield a return value of 0.

It works by searching a substring within a string by beginning at a designated location. The function then returns the position where the substring is found, or else a value of zero if a substring is not found

Syntax of CHARINDEX() in SQL Server

Below is the Syntax of CHARINDEX() in sqlserver:

Here, the substring parameter represents the part of the text you want to find in the string.

The string parameter represents a constant string in which we want to search for the substring. The start_location represents the start location where the search begins. It can either be a whole number representing an integer or a big integer.

The start_location parameter is not mandatory. If it's left unspecified or if you input a value of zero or a negative number, the search by default begins right from the very beginning of the text (from index 1, as it follows 1-based indexing).

Parameter Values of CHARINDEX() in SQL Server

Return Values

The CHARINDEX() function in SQL Server returns the location (index) of the Substring if the substring you're searching for is found within the string but if a substring is not found within the string, then the CHARINDEX() function will return a value of 0 indicating that the substring was not found in the string.

Applicable to the following versions

The CHARINDEX() function is available and compatible with a range of SQL Server versions, making it a versatile tool for many database environments. You can utilize the CHARINDEX() function in the following SQL Server versions:

• SQL Server 2017
• SQL Server 2016
• SQL Server 2014
• SQL Server 2012
• SQL Server 2008 R2
• SQL Server 2008
• SQL Server 2005

Examples of CHARINDEX() in SQL Server

Searching a character using the CHARINDEX() function

In this example, we'll use the CHARINDEX() function to search for a specific character within a string. Let's say we have the string "Hello" and we want to find the position of the letter 'o'. So, we can do it in this way:

Syntax:

Example:

Output:

Searching a substring using the CHARINDEX() Function

In this example, we'll use CHARINDEX() to search for a substring within a string. Suppose we have the string "The quick brown fox" and we want to find the position of the word "brown". We can do it in this way:

Syntax:

Example:

Output:

Using “starting_position” parameter in CHARINDEX() Function

The CHARINDEX() function allows you to specify the starting position for your search. In this example, we'll see how to pass the starting_position parameter and search for the occurrence of 'o' in the string "Hello, world!". For this, we will start the search from position 3:

Syntax:

Output:

CHARINDEX() Function is case-insensitive

As we know the CHARINDEX() function performs a case-insensitive search. In this example, we'll see how to do that and we will search for the "WORLD" substring in the string "Hello, world":

Output:

Making the function case-sensitive using the COLLATE Clause

By default, CHARINDEX() is case-insensitive, meaning it treats uppercase and lowercase letters as the same when searching for a substring. However, in some situations, you may want to specifically look for an exact case match. Then in that case, you can use the COLLATE clause to specify a particular collation, in this case, Latin1_General_CS_AS. This collation is case-sensitive ('CS' stands for Case-Sensitive), and 'AS' stands for Accent-Sensitive (it considers accents as well). We can then use the CHARINDEX() function to search for @substringToFind within @string but with the COLLATE clause applied. This means that the search is now case-sensitive.

Example:

Output: