In this SQL Server tutorial, you will learn how to find the position of the substring in the string using the CHARINDEX function in SQL Server.
First, you will understand ‘what the CHARINEDX() function’ with its syntax, then the working of the CHARINDEX() function with an example.
After that, you will understand how the CHARINDEX() function works when a nonexistent substring is passed to it. Finally, using the collation, you will understand how to perform case-insensitive and case-sensitive searches.
CHARINDEX Function in SQL Server
The CHARINDEX function in SQL Server returns the starting position of the specified substring in the string. In simpler words, if you have a string and want to find the starting position of any substring (character expression), this function returns the position of that substring.
For example, if you want to find the starting position of the ‘United’ in the string ‘I live in the United States’, then CHARINDEX() function will return the position as 3. Because the substring ‘United’ starts from the third index.
The syntax is given below.
CHARINDEX(substringToFind, stringToSearch, startingLocationForSearching)Where,
- CHARINDEX(): A whole function returns the substring’s starting position.
- substringToFind: It is the substring or character expression whose starting position you want to know.
- stringToSearch: This is the source string that contains the substring that you want to find.
- startingLocationForSearching: The starting position where the search will start for the substring. If you forget to specify this option, the search begins by default from the beginning of the string.
The return type of the CHARINDEX() function is bigint when the source string has data type varchbinary(max), varchar(max), and navrchar(max), and if it has another kind of data type, then the return type is int.
- Also, remember, if you pass the stringToSerach and substringToFind as null values to the CHARINDEX() function, it returns the result as NULL.
Take an example and understand how CHARINDEX functions in SQL Server work.
Consider you have the string ‘I am an employee of Walmart’ and need to find the starting position of the substring ‘Walmart’, so use the query below to find the position of that substring.
SELECT
CHARINDEX('Walmart', 'I am an employee of Walmart', 4) AS SubstringPosition;
Look at the above output. The CHARINDEX() function returns the position of the substring ‘Walmart’ as 21, meaning the substring starts from the index 21 in the string.
To verify that, look at the string and count the number of characters, including spaces. While counting, you will find that the substring ‘Walmart’ starts from the index 21.
Now, you understand how the CHARINDEX function in SQL Server works. Now, move to the next concept, where we will find the nonexistent substring.
CHARINDEX Function in SQL Server Finding Position of Nonexistent Substring
If you have a substring that doesn’t exist in the source string, if you try to find that nonexistent substring, then CHARINDEX() function returns the position as 0.
For example, you have a string like ‘I am a citizen of Australia’ and need to know the starting position of the substring ‘belong’, so check the query below.
SELECT
CHARINDEX('Belong', 'I am a citizen of Australia',5) AS SubstringPosition;
The result contains the starting position for the substring ‘Belong’ as 0, meaning the ‘Belong’ word is not in the source string. So remember this kind of position as 0 is returned by the CHARINDEX() when the substring is not found in the source string.
Now you know what happens when the nonexistent substring is passed to the CHARINDEX function in SQL Server.
Performing Case-Insensitive Search using CHARINDEX Function in SQL Server
If you have a substring in lower or uppercase, you can find the starting position of that substring irrespective of the case. By default, CHARINDEX() performs case-insensitive search.
For example, if you have the same string, ‘I am a citizen of Australia’. Specify the substring as ‘CITIZEN’ ( note here that it is in uppercase) to find its starting position. Here, while searching the substring, the CHARINDEX() function doesn’t care about the case of the substring.
Run the query below to understand that.
SELECT
CHARINDEX('CITIZEN', 'I am a citizen of Australia') AS SubstringPosition;
In the result, the starting position of the substring ‘CITIZEN’ is 8, and the case of the word ‘CITIZEN’ is different from the word ‘citizen’ in the source string.
But also, we haven’t specified the starting location from where the search for the substring should start; remember, when you don’t specify the starting location for the substring search, then by default, the search is started from the beginning of the string.
As a result, while searching for the substring in the source string, the CHARINDEX function in SQL Server treats the words ‘sql’ and ‘SQL’ as the same.
But if you want to perform the search based on the case-sensitive, you can do that by specifying the collation. For that, go to the next topic.
Performing Case-Sensitive Search using CHARINDEX Function in SQL Server
To perform a case-sensitive search for the substring, use the case-sensitive collation. Collation is a set of rules that decide how data should be sorted and compared.
So, by specifying case-sensitive collation in the CHARINDEX() function, you can make the CHARINDEX() function perform a case-sensitive search.
For example, suppose you have the string ‘Learning SQL Server’ and want to find the substring ‘sql’ for the same word in the same case.
For that, use the below query.
SELECT
CHARINDEX('sql' , 'Learning SQL Server' COLLATE Latin1_General_BIN ) AS SubstringPosition;
As you can see, the query returns the result as 0, meaning the substring ‘sql’ is not present in the source string.
But the substring ‘sql’ is present in the source if we look from a case-insensitive perspective. But here, we have used the collation as ‘Latin1_General_BIN’, which enforces the CHARINDEX() function to perform a case-sensitive search for the substring.
Thus, when the CHARINDX() function searches for the starting position of substring ‘sql’ in the string ‘Learning SQL Server’, it treats the words ‘sql’ and ‘SQL’ as different, meaning the substring is not present in the source string.
This is how to perform the case-sensitive search for the substring using the collation in the CHARINDEX function in SQL Server.
Conclusion
In this SQL Server tutorial, you learned how to find the starting position of the specific substring in the string using the CHARINDEX function in SQL Server.
Also, you learned that the CHARINDEX() function returns the 0 for the nonexistent substring, and then you performed the case-sensitive and case-insensitive search.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.