SQL Server CHARINDEX() Function

The CHARINDEX function in SQL Server is useful for working with and manipulating the string data. It is most commonly used to locate the beginning position of a substring or expression in a string. In simple words, we can say that this function is used to search the specified character or characters inside a string from a specified location. A character could appear in multiple positions in a string. This function will return the first character location in a string and ignores the rest of the matching character positions.

Let’s understand the concept of this function using the below example.

The online portal for a recruitment firm is an excellent example to explain its concept. The recruitment firm collects each student or customer details and enrolls them for placement assistance. Suppose the firm’s owner needs to see whether each employee’s email address is in the correct format or not; in that case, they can do this by using the CHARINDEX() function. For example, they can verify that the character ‘@’ is present in the entered mail id or not.

Syntax

The following is a basic syntax to illustrate the CHARINDEX function in SQL Server:

CHARINDEX ( expression_to_find , expression_to_search [ , starting_position  ] )  

We can see the following parameters in the CHARINDEX function:

expression_to_find: It is a required parameter to define a character or string that we are going to find in another string.

expression_to_search: It is a required parameter that indicates a string or expression in which we are going to search the substring.

starting_position: This is an optional parameter that takes an integer value to specify the starting position of the searched string. We are required to specify a starting position if we want to search for a substring in a given string. However, if we will not specify a value for this parameter, it will, by default, start the searching at index position 0. This function will also begin searching of string from starting if we specify it with zero or negative value.

Returns

• This function returns the location of a substring inside a given expression or string.
• The function will return 0 if the substring is not contained in the string.

Supported Versions

The CHARINDEX function will be applicable in the following SQL Server versions:

SQL Server 2019, SQL Server 2018, SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse.

NOTE: The CHARINDEX function can perform searching without bothering case-sensitive or case-insensitive characters.

CHARINDEX Function Example

Let us understand the working of the CHARINDEX function in SQL Server.

1. CHARINDEX function for a single search

The below example will use the CHARINDEX function to found a search string ‘SQL’ in the given string ‘SQL Tutorial Javatpoint’ and returns its position:

SELECT CHARINDEX('SQL', 'SQL Tutorial Javatpoint') AS Position;  

Executing the statement will display the below output:

2. Search a character position in a string

This example will give the location of @ character in the specified email address.

SELECT CHARINDEX ('@','[email protected]') as 'CharacterPosition'  

Executing the statement will display the below output:

3. Perform a case-insensitive search in a string

This example will return the position of the searched string in the specified string without bothering case sensitiveness:

SELECT CHARINDEX('SERVER', 'SQL server Tutorial Javatpoint') AS Position;  

Executing the statement will display the below output:

4. Perform a case-sensitive search using the COLLATE clause

This example shows a case-sensitive search for the string ‘SERVER’ in searched string ‘SQL server Tutorial Javatpoint‘ using the COLLATE clause.

SELECT CHARINDEX('SERVER',   

 'SQL server Tutorial Javatpoint'   

  COLLATE Latin1_General_CS_AS  

) AS Position;

Executing the statement will display the below output because it doesn’t contain the string “SERVER” in the specified string.

5. Perform a search for non-existent substrings

The below example perform searching for the substring ‘JavaScript’ in the string ‘Javatpoint is best for learning java’:

DECLARE @str VARCHAR(50);    

SELECT @str = 'Javatpoint is best for learning java';    

SELECT CHARINDEX('JavaScript', @str) AS Position;

Executing the statement will display the below output:

6. Perform a search for multiple matching substrings

The below example performs searching for the substring that has multiple matching in the specified string. Here we want to search for java and find its position.

DECLARE @str VARCHAR(50);    

SELECT @str = 'It is best to learn java on Javatpoint. Java is best for backend programming';    

SELECT CHARINDEX('Java', @str) AS Position;

Executing the statement will return the first matched substring position:

7. CHARINDEX function to search from a specific position

In this example, we are going to use the starting_position parameter to start the searching of substring ‘for’ at the tenth character of the given string:

SELECT CHARINDEX ('for', 'Love for all, Hate for none', 10) As Position;  

Executing the statement will display the below output: