SQL CHARINDEX | SQL Server CHARINDEX() Function
Summary: in this tutorial, you will learn learn SQL CHARINDEX() Function and how to use the SQL CHARINDEX() function to search for a substring in a string.
Definition and Usage
The SQL CHARINDEX() function searches for a substring in a string and it returns the position. Therefore, if the substring is not found, then the function returns 0.
Furthermore, The CHARINDEX() function searches for a substring inside a string starting from a specified location. Therefore, it returns the position of the substring found in the searched string, or zero if the substring is not found then the starting position returned is 1-based, not 0-based.
SQL CHARINDEX() Function Synatx
There are the following synatx:
Parameter Values
| Parameter | Description | |
|---|---|---|
| substring | Required. Substring is the substring to search for. Therefore, its length is limited to 8,000 characters. | |
| string | Required. The string can be a literal string, expression, or column. It is a string to search. | |
| start_location | Optional. The start_location is that location at which the search starts. Therefore, the start_location is an integer, big integer, or an expression that evaluates to a value of those data types. Furthermore, Here the start_location parameter is optional. If it is skipped, zero, or negative value, then the search starts at the beginning of the string. |
Technical Details
| Works in: | SQL Server (starting with 2008), Azure SQL Data Warehouse, Azure SQL Database, and Parallel Data Warehouse |
SQL CHARINDEX() Function Examples
Let’s see and understand the few examples of using the CHARINDEX SQL function.
A) First: To perform a single search using CHARINDEX SQL Function
The given following example uses the CHARINDEX() function to perform a simple search of the string ‘Server’ in the 'SQL Server Management Studio'
|
1 2 |
SELECT CHARINDEX('Server', 'SQL Server Management Studio') position; |
Therefore, let’s execute the query to return the output.
|
1 2 3 4 |
position ----------- 5 (1 row affected) |
B) Second: To perform a case-insensitive search using CHARINDEX() function
This statement shows a case-insensitive search for the string 'MANAGEMENT' in 'SQL Server Management Studio':
|
1 2 3 4 5 |
SELECT CHARINDEX( 'MANAGEMENT', 'SQL Server Management Studio' ) position; |
Therefore, let’s execute the query to return the output.
|
1 2 3 4 |
position ----------- 12 (1 row affected) |
C) Third: To perform a case-sensitive search using CHARINDEX SQL function
The following example shows a case-sensitive search for the string 'MANAGEMENT' in searched string SQL Server Management Studio.
|
1 2 3 4 5 6 |
SELECT CHARINDEX( 'MANAGEMENT', 'SQL Server Management Studio' COLLATE Latin1_General_CS_AS ) position; |
|
1 2 3 4 |
position ----------- 0 (1 row affected) |
D) Fourth: To search for a nonexistent substring using CHARINDEX() function
The following example illustrates a search for the substring 'teacher' in the string 'School is Education Hub'
|
1 2 3 |
DECLARE @hub VARCHAR(100); SELECT @hub = 'School is Education Hub'; SELECT CHARINDEX('teacher', @hub); |
Therefore, let’s execute the query to return the output.
|
1 2 3 4 |
position ----------- 0 (1 row affected) |
E) Fifth: To searching from a specific position using CHARINDEX() function
This example uses the start_location parameter to start the search for 'is' at the five and ten character of the string 'This is a my cousion sister':
|
1 2 3 |
SELECT CHARINDEX('is','This is a my cousion sister',5) start_at_fifth, CHARINDEX('is','This is a my cousion sister',10) start_at_tenth; |
Therefore, let’s execute the query to return the output.
|
1 2 3 4 |
start_at_fifth start_at_tenth -------------- -------------- 6 23 (1 row affected) |
Conclusion
In this tutorial, you have learned SQL CHARINDEX Function and how to use them to search for a substring in a string starting from a specified location and return the position of the substring.
About Author
