intro
Learn how to use the SQL Server CHARINDEX function to locate substrings, handle case sensitivity, specify start positions, and streamline string manipulation in your T-SQL queries.
Searching for a substring within a string is a common operation when working with strings in databases. To support that, SQL Server provides the CHARINDEX function, which allows you to locate substrings and perform advanced string manipulation in your T-SQL queries.
In this guide, you'll learn what the SQL Server CHARINDEX function is, how it works, and how to use it in real-world scenarios.
What Is CHARINDEX in SQL Server?
In SQL Server, CHARINDEX is a function used to search for a substring within a string and return the position (index) where that substring starts. This function is very handy for string processing tasks, such as finding the location of a certain character or word in a text.
How to Use the SQL Server CHARINDEX Function?
Below is the syntax of the CHARINDEX SQL Server function:
1
CHARINDEX(search_expression, target_expression [, start_location])
Where:
CHARINDEX SQL Server Examples
This is a quick example of how to use CHARINDEX in SQL Server:
1
SELECT CHARINDEX('world', 'Hello world') AS position;
Executing the above query in a powerful SQL Server database client like DbVisualizer will result in an output of “7” in the position column:
As you can see, the result will be 7. That’s because the word "world" starts at the 7th character of "Hello world" if we count from 1 (spaces also count as a character.)
Note: If the substring is not found, CHARINDEX returns 0.
Another example:
1
SELECT CHARINDEX('SQL', 'Learn SQL on SQLServer', 1) AS firstOccur,
2
CHARINDEX('SQL', 'Learn SQL on SQLServer', 8) AS secondOccur;
In this case:
Verify that by executing the query:
Now, suppose you want to run:
1
SELECT CHARINDEX('sql', 'I love SQL Server') AS pos1,
2
CHARINDEX('SQL', 'I love SQL Server') AS pos2;
The result—assuming default case-insensitive collation—will be:
If case-sensitive collation, pos1 might be 0 if the case doesn't match exactly and pos2 = 8.
Another popular example is the use of CHARINDEX with an SQL variable as below:
1
DECLARE @name NVARCHAR(100) = 'John Doe';
2
SELECT SUBSTRING(@name, 1, CHARINDEX(' ', @name) - 1) AS FirstName,
3
SUBSTRING(@name, CHARINDEX(' ', @name) + 1, 100) AS LastName;
This uses the space position to separate first and last name.
Key Notes About the SQL Server CHARINDEX Function
Use Cases of CHARINDEX in SQL Server
Simple Search
Find if a substring exists and where. E.g., find the position of '@' in an email to separate username and domain:
1
SELECT CHARINDEX('@', email) as atPos FROM Users;
If atPos is 0, you know the email string had no '@' (which would be invalid email format).
Extracting Substrings
Often used with other functions like SUBSTRING to extract text around a found position. For example, you can obtain the text before a comma:
1
DECLARE @txt NVARCHAR(100) = 'LastName, FirstName';
2
SELECT SUBSTRING(@txt, 1, CHARINDEX(',', @txt) - 1) AS LastName;
3
-- This takes everything from the start up to just before the comma.
Above, we used the SQL Server CHARINDEX function to find the comma, then SUBSTRING from 1 to the found position - 1.
Searching After a Certain Point
The third argument allows you to find subsequent occurrences. For instance, to find the second occurrence of a substring, you can use a CHARINDEX function starting after the first occurrence:
1
DECLARE @str NVARCHAR(50) = 'abc abc abc';
2
-- Find first space:
3
SELECT CHARINDEX(' ', @str) AS firstSpace; -- returns 4
4
-- Find second space by starting just after first:
5
SELECT CHARINDEX(' ', @str, CHARINDEX(' ', @str) + 1) AS secondSpace; -- returns 8
This will yield the position of the second space.
Usage in the WHERE Clause
Last but not least, you can filter rows based on the output of CHARINDEX, e.g.:
1
SELECT * FROM Products
2
WHERE CHARINDEX('XL', product_name) > 0;
This finds all products where the name contains "XL". (Note: Using CHARINDEX like this will scan the text of every product_name. If you need fast searches inside text, consider a full-text search or at least indexing if possible on patterns, but simple CHARINDEX is fine for moderate scenarios.)
CHARINDEX In Other Databases
While CHARINDEX is specific to T-SQL, other databases have similar functions:
Understanding the SQL Server CHARINDEX function helps with string parsing tasks in T-SQL. For more complex scenarios, it’s often used alongside SUBSTRING, LEN, PATINDEX (which is like CHARINDEX but for patterns/wildcards).
Conclusion
In summary, CHARINDEX is your go-to function in SQL Server for finding a substring’s position within a larger string. It returns an integer location, or 0 if not found. It’s straightforward to use and very useful for string manipulation tasks in T-SQL. Keep in mind the 1-based indexing and the effect of collation on case sensitivity when using it. Also keep in mind that CHARINDEX can assist you in a wide variety of use cases ranging from simple search queries to searches after a certain point.
As shown here, running queries becomes easier with a visual database client like DbVisualizer. This is a fully-featured database client that supports multiple DBMS technologies, offers advanced query optimization capabilities, and can generate ERD-type schemas with a few clicks. Try it for free, and until next time.
