What is CHARINDEX() in SQL?
Learn via video course
FreeThe CHARINDEX() function in SQL is used to find the location of the specified character in the given expression. It searches for the first occurrence of a specified substring within a given string from a specified position, and returns the index of the first character of the substring found.
The following diagram depicts the working of charindex in SQL :
Return value: The CHARINDEX() function in SQL returns the index of the first appearance of the given expression and ignores the rest of the string.
We can use the CHARINDEX() function in SQL to perform both case insensitive and case sensitive lookups.
Syntax
The following is the syntax of the CHARINDEX() function in SQL:
Parameters
- expression:
The expression refers to the substring value that is being searched within the given string. The length of the expression cannot be more than characters. - stringVal :
The stringVal refers to the string within which the expression is being searched. The stringVal can be an expression, a column, or a literal string. - beginningLocation :
The beginningLocation refers to the index of the stringVal from where the search for expression will start. The beginningLocation is an integer value. It is an optional parameter thus if it is not passed or if the value passed is negative then the search starts from the beginning of the string.
Examples For CHARINDEX in SQL
Example 1: Searching a Character Using the CHARINDEX() Function
In this example, we will find the location of the first appearance of a character within a given string.
Code:
Output:
Explanation of the example: In the above example, We have passed the character 'a' as the expression to search from. The charindex in SQL starts searching from index 0 (since the beginningLocation is not mentioned) and finds the first appearance of 'a' at index 8. Thus it returns 8 and aborts the search.
Example 2: Searching a Substring Using the CHARINDEX() Function
In this example, we will find the location of the first appearance of a string within a given string.
Code:
Output :
Explanation of the example:
In the above example, We have passed the string 'is' as the expression to search from. The charindex in SQL starts searching from index 0 (since the beginningLocation is not mentioned) and finds the first appearance of 'is' at index 5. Thus it returns 5 and aborts the search.
Note :
that the charindex in SQL will return the index at which the "is" string will start.
Example 3: If the Substring doesn’t Match with the Given String
In this example, we will again try to find the location of the first appearance of a string within a given string.
Code :
Output :
Explanation of the example :
In the above example, We have passed the string 'peter' as the expression to search from. The charindex in SQL starts searching from index 0 (since the beginningLocation is not mentioned) but could not find the appearance of 'peter' in the string. Thus it returns 0.
Example 4: Use of “starting_position” Parameter in charindex() Function
In this example, we will find the location of the first appearance of a string within a given string from a certain position.
Code :
Output :
Explanation of the example :
In the above example, We have passed the string 'is' as the expression to search from. The charindex in SQL starts searching from index 10 (since the beginningLocation has been passed as 10, thus the charindex in SQL will skip the first 9 indexes) and finds the first appearance of 'is' at index 39. Thus it returns 39 and aborts the search.
Note : The string "is" was also present at index 5 but the search started from index 10 thus it got skipped.
Example 5: Showing that charindex() Function is Case-Insensitive
In this example, we will find the location of the first appearance of a string within a given string.
Code :
Output :
Explanation of the example :
In the above example, We have passed the string 'Is' as the expression to search from. The charindex in SQL starts searching from index 0 (since the beginningLocation is not mentioned) and returns 5. This is because by default the charindex in SQL is case insensitive thus it will treat both is and Is can the same and return 5.
Example 6: Making the Function Case-Sensitive Using the COLLATE clause
In this example, we will take the above case and make the charindex in SQL case sensitive using the COLLATE clause.
Code :
Output :
Explanation of the example :
In the above example, We have passed the string 'IS' as the expression to search from. The charindex in SQL starts searching from index 0 (since the beginningLocation is not mentioned) and returns 0. This is because although the string 'is' is present in the given string but its case is different from that passed in the expression. The charindex in SQL now being case-sensitive after the use of COLLATE Latin1_General_CS_AS will treat is and IS differently and return 0 in the given case.
Learn More
To learn more about how the index works in SQL please visit this link.
Conclusion
- The charindex in SQL is used to return the location of a character or string within an expression.
- The charindex in SQL returns the index of the first appearance of the given expression.
- The charindex in SQL can be used to perform both case insensitive and case sensitive lookups.
- We can specify the location from where the search can start in the string.