The task of finding a character string within a larger character string in a SQL Server database is very easy. SQL Server has many built-in system functions that can be used for character string manipulation, one of which is the CHARINDEX function.
We use the CHARINDEX system function to locate a string within a string!
In this very brief tutorial, we’ll discuss how we can use the CHARINDEX system function to find a string within a string in a Microsoft SQL Server database. We’ll discuss these topics:
- The CHARINDEX system function
- Examples of using CHARINDEX to find a string within a string
- Tips and tricks
Take it from the top:
1. The CHARINDEX system function
The CHARINDEX system function returns the location of a substring within a larger string. It is typically used in a SELECT statement.
The syntax is very easy:
CHARINDEX('<substring-to-look-for>', '<string-to-search-within>', <optional-starting-location>)
We’ll talk about each parameter of the function:
- ‘<substring-to-look-for>’ – This is the substring you want to search for within the larger string.
- ‘<string-to-search-within>’ – The typically larger string that you want to search within.
- <optional-starting-location> – This is simply a number representing the point within the larger string that you want to start searching for the substring. As noted, this parameter is optional.
CHARINDEX will return an integer representing the location that the substring starts within the larger string.
The best way to understand how we can use CHARINDEX to find a string within a string is to look at some examples.
2. Examples of using CHARINDEX to find a string within a string
As mentioned earlier, the CHARINDEX system function is typically used in a SELECT statement. Take a look at the following example that simply uses two string literal values within the CHARINDEX function in a SELECT statement:
SELECT CHARINDEX('spagetti', 'Green eared spagetti monster') as [Location of Spagetti]
This SELECT statement will use CHARINDEX to search for the string ‘spagetti’ within the larger string ‘Green eared spagetti monster’.
The function will return the starting position of that substring within the larger string. If we look at the string ‘Green eared spagetti monster’, it looks like the word ‘spagetti’ starts at position 13. The first letter of the word ‘spagetti’ starts at the 13th character of the larger string.
Here is the result set if we run this within a query window:
Easy peasy.
The optional third parameter
As mentioned earlier, you can add an optional third parameter to your call to CHARINDEX. This third parameter is simply an integer representing the location within the larger string where you want SQL Server to start looking. If this parameter is left out, SQL Server simply starts looking at the beginning of the larger string.
This is a good trick if you want to save SQL Server from looking through characters that you know won’t contain the substring. For example, maybe we know that the word ‘spagetti’ doesn’t start within the first 10 characters. We can outline the integer 10 as our third parameter to save SQL Server some time. The final result is the same, too:
The input string values can be any string expression
The last thing I want to point out is how your input string values can be any string expression. They obviously don’t need to be hard-coded string literal values. For example, a column name can be used in the CHARINDEX function.
Take a look at the following rows in a Products table:
We can use the ProductName column within a call to CHARINDEX to find the location of the word ‘Table’ within these rows:
Superb!
3. Tips and tricks
Here is a list of some helpful tips and tricks you should know when working with the CHARINDEX system function:
Tip # 1: CHARINDEX returns zero if the substring was not found
If the substring simply was not found within the larger string, the CHARINDEX function returns zero.
For example, the word ‘Table’ does not appear in all our products:
For those rows where the word ‘Table’ does not appear in the ProductName column, zero is returned!
Let’s think about something important. When CHARINDEX returns 0, there might be some people who think that this means the substring is located at the very beginning of the larger string. These people assume the character positioning is 0-based, meaning the first character has a position of 0. This leads us to our next tip:
Tip # 2: Character positioning is 1-based, not 0-based
The first character in your larger string is at position 1, NOT 0. For those of us from a programming background, we are used to string values or arrays starting with an index of 0. But we need to forget about that. When it comes to CHARINDEX, we start with 1!
Knowing this, I might choose to enhance this query even further by displaying something other than 0 when the substring is not found. We could use some other handy tools to present the information in a more meaningful way:
Learn more about these tools here:
Tip # 3: The CHARINDEX function will return NULL if either input string is NULL
Like many things in SQL Server, NULL will throw things off if it’s used as an input value to CHARINDEX. If either input string value is NULL, the CHARINDEX function returns NULL. Take a look at the following simple example:
This scenario is more likely to happen if you use an expression in either parameter. If you have an expression that returns NULL, then try to use that as a parameter to CHARINDEX, your output will be NULL!
Next Steps:
Leave a comment if you found this tutorial helpful!
Check out the full tutorial on character string data types in Microsoft SQL Server:
SQL Server character data types (and the differences between them)
Knowing the differences between the various character data types is very important when it comes to developing databases.
Do you know the difference between CHAR and VARCHAR? Click the link to find out!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
