The CHARINDEX function in SQL Server is used to find the position of a substring within a string. It returns the starting position of the first occurrence of the specified substring. If the substring is not found, CHARINDEX returns 0.
Here’s the basic syntax for the CHARINDEX function:
CHARINDEX(substring, string, start_position)
- substring: The substring you want to find within the string.
- string: The string in which to search for the substring.
- start_position (optional): The position in the string from which to start the search. If this parameter is omitted, the search starts from the beginning of the string.
Example:
Let's look at an example of how to use CHARINDEX:
DECLARE @string NVARCHAR(100) = 'Hello, welcome to SQL Server';
DECLARE @substring NVARCHAR(50) = 'welcome';
SELECT CHARINDEX(@substring, @string) AS SubstringPosition;
In this example, the CHARINDEX function will return the starting position of the substring "welcome" within the string. The result for this query would be:
SubstringPosition
------------------
8
Using the start_position parameter:
You can also specify a starting position to search from. For example:
DECLARE @string NVARCHAR(100) = 'Hello, welcome to SQL Server. Welcome again!';
DECLARE @substring NVARCHAR(50) = 'welcome';
-- Search starting after the first occurrence of 'welcome'
SELECT CHARINDEX(@substring, @string, CHARINDEX(@substring, @string) + 1) AS SubstringPosition;
In this case, the search for "welcome" starts again right after the first occurrence, which results in:
SubstringPosition
------------------
27
Summary
The CHARINDEX function is very useful for string searching in SQL. Remember:
- It returns the position of the first occurrence (1-based index).
- Returns 0 if the substring is not found.
- Optionally, you can specify a starting position for your search.