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
|
1 |
CHARINDEX ( expression_to_find , expression_to_search [ , start_location ] ) |
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.
|
1 |
SELECT CHARINDEX ('@','rajendra.gupta16@gmail.com') as 'CharacterPosition' |
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).
|
1 |
SELECT CHARINDEX('.','rajendra.gupta16@gmail.com',11) as 'CharacterPosition' |
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.
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.
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.
|
1 2 3 |
DECLARE @ExpressionToSearch varchar(100) SET @ExpressionToSearch = 'Explore SQL Server on SQLShack with Rajendra Gupta articles' SELECT CHARINDEX ('Rajendra', @ExpressionToSearch) AS 'CharacterPosition' |
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.
If an exact match is not found it returns 0 in the output.
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.
|
1 2 3 |
DECLARE @ExpressionToSearch varchar(100) SET @ExpressionToSearch = 'Explore SQL Server on SQLShack with Rajendra Gupta articles - SQLShack' SELECT CHARINDEX ('SQLShack', @ExpressionToSearch) AS 'CharacterPosition' |
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.
|
1 2 3 |
DECLARE @ExpressionToSearch varchar(100) SET @ExpressionToSearch = 'Explore SQL Server on SQLShack with Rajendra Gupta articles - SQLShack' SELECT CHARINDEX ('SQLShack', @ExpressionToSearch) AS 'CharacterPosition' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @Name AS VARCHAR(100)= 'Explore SQL Server with articles on SQLShack'; SELECT CASE WHEN CHARINDEX('SQLShack', @Name) > 0 THEN 'Exists' ELSE 'Not Exists' END AS FindSubString; SELECT CASE WHEN CHARINDEX('Rajendra', @Name) > 0 THEN 'Exists' ELSE 'Not Exists' END AS FindSubString; |
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
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.
|
1 2 3 |
DECLARE @ExpressionToSearch varchar(100) SET @ExpressionToSearch = 'Explore SQL Server on SQLSHACK with Rajendra Gupta articles - SQLShack' SELECT CHARINDEX ('sqlshack', @ExpressionToSearch) AS 'CharacterPosition' |
It does not perform case sensitive search, and we still get the correct output.
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.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @ExpressionToSearch varchar(100) SET @ExpressionToSearch = 'Explore SQL Server on SQLSHACK with Rajendra Gupta articles ' SELECT CHARINDEX ('sqlshack', @ExpressionToSearch COLLATE Latin1_General_CS_AS) AS 'CharacterPosition' SELECT CHARINDEX ('SQLShack', @ExpressionToSearch COLLATE Latin1_General_CS_AS) AS 'CharacterPosition' SELECT CHARINDEX ('SQLSHACK', @ExpressionToSearch COLLATE Latin1_General_CS_AS) AS 'CharacterPosition' |
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.
|
1 2 3 4 5 |
SELECT TOP 10 [EmpName], CHARINDEX('R', empname) AS "Position of R", [City], [Designation] FROM [SQLShackDemo].[dbo].[Employee]; |
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.
Let’s update one record in Employee table and replace empname with NULL.
|
1 |
Update [SQLShackDemo].[dbo].[Employee] set EmpName=NULL where empname='Charlotte Robinson' |
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 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.
|
1 2 3 4 5 |
SELECT TOP 10 [EmpName],EmpID, CHARINDEX('1', empid) AS "Position of 1", [City], [Designation] FROM [SQLShackDemo].[dbo].[Employee]; |
We need to specify numeric value as well in single quotes. If we do not put single quotes, it gives following error message.
|
1 2 |
Msg 8116, Level 16, State 1, Line 2 Argument data type int is invalid for argument 1 of charindex function. |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023