How to use SQL Server T-SQL function CHARINDEX in SQL queries

This article will explain the SQL Server string function CHARINDEX and its use cases. This function returns the first position of a specified string in the first argument from another input string specified in the second argument.
The syntax of this function is given in the below SQL code.

-- Syntax
CHARINDEX (expressionTOfind, expressionTOsearch, starting_position)

Here,
expressionTOfind is a character expression or substring that needs to be searched from another string specified in the expressionTOsearch argument

expressionTOsearch is a character expression in which the first argument specified under expressionTOfind will be searched

starting_position is an optional argument. If this argument is specified, then the search will start after this value position.

If we specify a NULL value for any of the argument expressionTOfind or expressionTOsearch then the CHARINDEX function will also return its output as NULL and it will return 0 if expressionTOfind will not be there in expressionTOsearch.

Use cases of T-SQL function CHARINDEX

I have given 5 use cases of this function in the below section. You can use SQL Server management studio or dbForge SQL manager to practice these use cases or for your other development activities.

Let’s start with the first use case in which I have searched a string from a specified input string.

Simple use case of CHARINDEX with the input string

There are two statements in the below example. The first statement is used to search the starting position of expression “Manvendra” which is the expressionTOfind argument from another input string “Name – Manvendra Deo Singh. Manvendra lives in Gurgaon” which is the expressionTOsearch argument.

I have not specified starting_position argument in the first statement whereas I have specified this argument in the second statement as 10 keeping the rest of all arguments as it is.

By default, CHARINDEX will start searching the pattern from starting position 1 of any string if you will not specify the starting_position argument in this function. We can validate it in the below result.

-- Return starting position of Manvendra in a string
SELECT CHARINDEX(‘Manvendra’, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’),
    CHARINDEX(‘Manvendra’, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’,10)

The first statement searches the specified substring “Manvendra” from the given input string from starting position 1 as we have not mentioned any start position whereas the second statement will search the same pattern from starting position 10 as I have specified the starting_position argument value as 10 and that is why there is different output for same pattern search given in the above use case.

The starting position of substring “Manvendra” is returning as 8 in the first statement whereas it is returning as 29 if we specify the starting_position argument to 10 in this example.

The starting_position argument allows this function to skip all characters before the specified value from the search operation and start searching from the value specified under this argument.

I will take another example in which I will show you the result if we pass the NULL value to its arguments.

As I have already mentioned above If we specify a NULL value for any of the value expressionTOfind or expressionTOsearch then the CHARINDEX function will also return its output as NULL and it will return 0 if expressionTOfind will not be there in expressionTOsearch.

Let’s verify the above statement by executing the below T-SQL statements. Here, I have specified NULL value as expressionTOfind in the first statement whereas “SQL” in the second statement along with keeping the input string as the same in which searching will be done.

-- Use CHARINDEX with NULL
SELECT [NULL Position] = CHARINDEX(NULL, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’),
    [SQL Position] = CHARINDEX(‘SQL’, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’),

Here is the output. We can see if we pass NULL value then CHARINDEX function is returning as NULL whereas if we search the starting position of a string which is not there in the second argument then its output is returning as 0.

We can revalidate by looking at the input string and find that the “SQL” string is not there in the second argument.

The SQL function CHARINDEX will also return the output if we use wildcards to search the characters as I have searched in the below example.

SELECT CHARINDEX(‘-‘, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’),
CHARINDEX(@, ‘Email – [e-mail]’)

You can see the starting position of both characters in the output section.

Use CHARINDEX with variable

This section will show how to use SQL function CHARINDEX with variables.

Here, I have declared a variable @input_string and assigned a string to this variable.

Next, I used this function CHARINDEX to search starting position of various characters by specifying the @input_string variable and not the full string.

Have a look at the T-SQL statements.

DECLARE @input_string varchar(50)
SET @input_string = ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon. Email – [e-mail]’
SELECT [Gurgaon Position] = CHARINDEX(‘Gurgaon‘, @input_string),
    [@ Position] = CHARINDEX(‘@’, @input_string),
    [SQL Position] = CHARINDEX(‘SQL’, @input_string)

I have searched starting position of 3 different character\strings “Gurgaon”, “@” and “SQL”. We can the result set for each character in the below screenshot.

I have taken another example in which I have also specified 3rd argument starting_position from where the search will start to find the specified expressionTofind expression from variable @input_string.

I have used the same expressionTofind expression as “Man” and specified different values for the 3rd argument starting_postion.

We will get the below output post executing the above SQL statements. We can see the output is returning different values for the same substring search because of specifying the 3rd argument starting_position.

Case sensitive search using CHARINDEX

We can also search a substring using a specific collation by defining COLLATE statement with the desired collation name.

Have a look at the below example in which I have specified 2 different collations using COLLATE statement in function CHARINDEX and searched the same substring “Gurgaon” with case sensitive and insensitive options with their respective collations.

Here, the first statement is searching “gurgaon” keeping the case insensitive as I have specified collation as Lation1_General_CI_AS whereas the rest of the two searches “GURGAON” and “gurgaon” are searched with case sensitive collation as shown in the below example.

DECLARE @input_string varchar(50)
SET @input_string = ‘Name – Manvendra Deo Singh. Manvendra lives in GURGAON. Email – [e-mail]’
SELECT [gurgaon Position with CI] = CHARINDEX(‘gurgaon‘, @input_string COLLATE Lation1_General_CI_AS),
 [GURGAON Position with CS] = CHARINDEX(‘GURGAON‘, @input_string COLLATE Lation1_General_CS_AS),
[gurgaon Position with CS] = CHARINDEX(‘gurgaon‘, @input_string COLLATE Lation1_General_CS_AS)

When we go with case sensitive search then the exact text or string should match. You can see the input string has string “GURGAON” so a search where “GURGAON” is specified has returned the result whereas another search “gurgaon” has returned as 0 whereas when we mention case insensitive collation then lower-case substring “gurgaon” has also returned the correct result.

Use CHARINDEX with the table column

We can also use the SQL function CHARINDEX with column names. Here, I will show you an example in which I will use system table sys.databases and returned the list of all databases and the starting position of a character given in their names.

SELECT name, create_date, CHARINDEX(‘DB‘, name) AS [Position of DB]
FROM sys.databases
Go

Here, I have searched the character “DB” in the column name of the system table sys.databases. It will return the list of all databases and the starting position of the characters DB if it is there in the database names. If string DB will not be found in the database name then the output will return as zero.

Use CHARINDEX in the WHERE clause

This use case will demonstrate using the SQL function CHARINDEX in the WHERE clause to define a condition. The below example will return the list of all databases whose name has character DB at more than 4th position.

SELECT name, create_date 
FROM sys.databases
WHERE CHARINDEX(‘DB‘, name)>4

You can validate the below output in which we can see all databases which have the “DB” character in their names after the 4th position.

Conclusion

This article will help you understand one of the SQL Server string functions CHARINDEX. You can go ahead and test the above use cases to learn this function and use it in your business requirements. Please share this article and comment with us your feedback in the comment section so that we can improve in a better way.