Rajendra Gupta
SQL CASE statement

SQL CHARINDEX

May 9, 2019 by

We use the SQL CHARINDEX function to find the position of a substring or expression in a given string. We might have a character in different positions of a string. SQL CHARINDEX returns the first position and ignores the rest of matching character positions in a string.

SQL CHARINDEX Function Syntax

It takes following parameters in SQL CHARINDEX function.

  • expression_to_find: In this parameter, we specify a character or string that we want to search in another string
  • expression_to_search: We can specify a string or sentence in which we want to search expression_to_find
  • start_location: It is an optional parameter. We can specify an integer value in this parameter to specify start location. If we want to search expression_to_find in expression_to_search with a specified start location, we can specify it. By default, if we do not mention any value in this parameter, it starts a search from the index position 0

Example 1: Search a character position in a string

In this example, we want to find position of @ character in a specified email address rajendra.gupta16@gmail.com

In the following screenshot, we can see position of each character in the email address string. The character ‘@’ is in position 17. We get this position in output of SQL CHARINDEX.

SQL CHARINDEX examples

Example 2: Use of optional parameter Start_position in SQL CHARINDEX

Similarly, let’s search dot (.) position in this string. We can see that the dot is on position 9th and 23rd in the specified string (email address).

Once we execute this script, it returns the first position of the dot in the output. It starts the search from starting position of a string and stops once it finds a suitable match.

SQL CHARINDEX examples

Suppose we want to get the position of the second dot (.) in this email address. We can specify a value for an optional parameter to start searching from a specific position. It starts from a specific character position and checks for the character position. We still get the actual position of the character that is 23rd in this example.

SQL Server CharIndex example

Example 3: Search a substring position in a specified string in SQL CHARINDEX

In previous examples, we searched a specific character in a specified string. We can also search a substring as well in a string.

In the following query, we declared a variable @ExpressionToSearch for the string and set a value on it. We want to search for substring Rajendra in this string.

It searches for the substring in a specified string. If it gets an exact match of the substring, it returns the starting position of the substring.

Search a substring position in a specified string

If an exact match is not found it returns 0 in the output.

Search a substring position in a specified string

Example 4: Search a substring position in a specified string with multiple matching in SQL CHARINDEX

Suppose we want to search a substring in a specified string. In the string we have multiple matching substrings. For example, in the following query, we want to search for SQLShack and find its position.

Search a substring position in a specified string with multiple matching

We can use start_location in SQL CHARINDEX with a substring as well. For example, let’s start with position 24 and see the result. It starts from character position 24 and searches for a particular substring. We can see the substring starting position is now at 63.

Search a substring position in a specified string with multiple matching

Example 5: SQL CHARINDEX with SQL CASE statement

We can use SQL CHARINDEX with SQL Case statement to search a particular substring existence in a specified string.

In the following screenshot, we can see that SQL CHARINDEX function checks for a particular substring. If it returns a value greater than 0 it means substring exists in specified string else it does not exist.

  • Substring SQLShack exists in a specified string that’s why the output is Exists
  • Substring Rajendra does not exist in a specified string that’s why the output is Not Exists

SQL CASE statement

Example 6: Case sensitive search with SQL CHARINDEX

In the previous examples, we did not use case sensitive search. For example, in the following query, we want to search for substring sqlshack in our string. This substring exists does but it exists in upper case.

It does not perform case sensitive search, and we still get the correct output.

SQL Server CASE statement

We can use collation to perform case sensitive search. In the following examples, we use COLLATE Latin1_General_CS_AS to perform case sensitive search. We need to note that all character case in a substring should match within a string. Execute the following query to understand this.

Case Sensitive search in SQL Server

Example 7: SQL CHARINDEX and table column

We can use SQL CHARINDEX for existing data in a table. We can use it to get output in a separate column. In the following example, we want to check the position of character R in empname column values of the Employee table.

In the following screenshot, we have a new column to get position of character R in EmpName column values. If Empname does not contain specified character R, it returns 0.

example of table column with SQL Server charindex

Let’s update one record in Employee table and replace empname with NULL.

Rerun the SQL CHARINDEX query, and we see value NULL in Position of R column. If we have a NULL value in a column, it also returns a NULL value.

example of table column with SQL Server charindex

Example 8: SQL CHARINDEX and Numeric value

We can search for numeric value as well as using SQL CHARINDEX. Suppose we want to find a position of character 1 in empid column of the employee table.

Search Numeric value in a string

We need to specify numeric value as well in single quotes. If we do not put single quotes, it gives following error message.

Quick Recap of SQL CHARINDEX

  • SQL CHARINDEX Returns a position of a substring within a string
  • If the target string varchar(max), nvarchar(max), it returns Big Int value else it returns Int data type
  • By default, it performs a case insensitive search
  • If there is no match found, it returns 0 in return

Conclusion

In this article, we explored SQL CHARINDEX function and its usage with various examples. Please feel free to provide feedback or ask questions in the comment section below.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
91 Views