CHARINDEX() function SQL Server
FREE
Overview
The CHARINDEX() function searches for substrings and provides their positions within the string, offering case-insensitive searches. This article explores CHARINDEX(), covering its syntax, parameters, return values, and compatibility across SQL Server versions. Examples demonstrate how CHARINDEX() finds characters and substrings, customizes search starting points, and manages case sensitivity in SQL queries.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
What is SQL Server CHARINDEX() Function?
The CHARINDEX() function in SQL Server is a valuable tool used to search for a specific substring within a given string and subsequently retrieve its position. In cases where the desired substring cannot be located within the string, the CHARINDEX() function will yield a return value of 0.
It works by searching a substring within a string by beginning at a designated location. The function then returns the position where the substring is found, or else a value of zero if a substring is not found
Note: The position returned here is 1-based, and not 0-based. Also, this function carries out a case-insensitive search. It means that the function doesn't pay attention to whether the letters in the text are written in uppercase or lowercase, instead, it treats them as if they were all the same.
Syntax of CHARINDEX() in SQL Server
Below is the Syntax of CHARINDEX() in sqlserver:
Here, the substring parameter represents the part of the text you want to find in the string.
Note: This substring parameter can have a maximum length of 8,000 characters.
The string parameter represents a constant string in which we want to search for the substring. The start_location represents the start location where the search begins. It can either be a whole number representing an integer or a big integer.
The start_location parameter is not mandatory. If it's left unspecified or if you input a value of zero or a negative number, the search by default begins right from the very beginning of the text (from index 1, as it follows 1-based indexing).
Parameter Values of CHARINDEX() in SQL Server
| Parameter | Mandatory/Optional | Description |
|---|---|---|
| substring | Mandatory | The specific substring that you want to locate. |
| string | Mandatory | The string in which you're performing the search. |
| start_location | Optional | The starting position within the text (default is 1). |
Return Values
The CHARINDEX() function in SQL Server returns the location (index) of the Substring if the substring you're searching for is found within the string but if a substring is not found within the string, then the CHARINDEX() function will return a value of 0 indicating that the substring was not found in the string.
Applicable to the following versions
The CHARINDEX() function is available and compatible with a range of SQL Server versions, making it a versatile tool for many database environments. You can utilize the CHARINDEX() function in the following SQL Server versions:
- SQL Server 2017
- SQL Server 2016
- SQL Server 2014
- SQL Server 2012
- SQL Server 2008 R2
- SQL Server 2008
- SQL Server 2005
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreExamples of CHARINDEX() in SQL Server
Searching a character using the CHARINDEX() function
In this example, we'll use the CHARINDEX() function to search for a specific character within a string. Let's say we have the string "Hello" and we want to find the position of the letter 'o'. So, we can do it in this way:
Syntax:
Example:
Output:
Turn Learning into Career Growth
Searching a substring using the CHARINDEX() Function
In this example, we'll use CHARINDEX() to search for a substring within a string. Suppose we have the string "The quick brown fox" and we want to find the position of the word "brown". We can do it in this way:
Syntax:
Example:
Output:
Using “starting_position” parameter in CHARINDEX() Function
The CHARINDEX() function allows you to specify the starting position for your search. In this example, we'll see how to pass the starting_position parameter and search for the occurrence of 'o' in the string "Hello, world!". For this, we will start the search from position 3:
Syntax:
Output:
CHARINDEX() Function is case-insensitive
As we know the CHARINDEX() function performs a case-insensitive search. In this example, we'll see how to do that and we will search for the "WORLD" substring in the string "Hello, world":
Output:
Making the function case-sensitive using the COLLATE Clause
By default, CHARINDEX() is case-insensitive, meaning it treats uppercase and lowercase letters as the same when searching for a substring. However, in some situations, you may want to specifically look for an exact case match. Then in that case, you can use the COLLATE clause to specify a particular collation, in this case, Latin1_General_CS_AS. This collation is case-sensitive ('CS' stands for Case-Sensitive), and 'AS' stands for Accent-Sensitive (it considers accents as well). We can then use the CHARINDEX() function to search for @substringToFind within @string but with the COLLATE clause applied. This means that the search is now case-sensitive.
Example:
Output:
Conclusion
- CHARINDEX() in SQL Server finds the position of a substring within a string.
- It returns the position if the substring is found; otherwise, it returns 0.
- By default, CHARINDEX() performs case-insensitive searches.
- If start_location is not specified or set to 0 or a negative value, CHARINDEX() starts from the beginning of the text.
- CHARINDEX() is compatible with SQL Server versions ranging from 2005 to 2017, making it accessible for a wide range of database environments.
- With a straightforward syntax structure (CHARINDEX(substring, string, start_location)), it's easy to incorporate CHARINDEX() into your SQL queries.
- The function's parameters (substring, string, and start_location) offer flexibility and control over your search.