By: Greg Robidoux
The CHARINDEX function is used to find the starting point where one string exists inside another string. This is often used with other functions such as SUBSTRING to find the starting point within a string.
Syntax
CHARINDEX(stringToFind, stringToSearch [,startingPosition])
Parameters
- stringToFind - This is the string you want to find in stringToSearch.
- stringToSearch - This is the string that contains the string you are searching for.
- startingPosition - The startingPosition is an optional parameter and is a number. If it is not specified the stringToSearch is started at the beginning. If a value is specified, the search begins that many characters into the stringToSearch.
Simple CHARINDEX Example
Below is a simple example of using CHARINDEX. We will search for the word "test" in the longer string "This is a test".
SELECT CHARINDEX('test', 'This is a test')
This returns a value of 11, since the value "test" starts at position 11 in the stringToSearch.
SELECT CHARINDEX('This', 'This is a test')
This returns a value of 1, since the value "This" starts at position 1 in the stringToSearch.
CHARINDEX Example Not Starting at First Position of String
Here is an example where we use a starting position. Since we are starting at position 15, it will skip the first "test" and look for any occurrences of "test" after that position.
SELECT CHARINDEX('test', 'This is a test, this is a test.', 15)
This returns a value of 27, since we are starting at position 15 and therefore it skips the first "test" and finds the second "test" at position 27 in the stringToSearch.
Find All Occurrences of String in a String Example
The following code is a query against the AdventureWorks database. It queries the Production.ProductDescription table to find all occurrences of a string in the Description column.
Below we are searching for the word "bike" and returning rows where the position is greater than 0.
DECLARE @searchValue nvarchar(20) = 'bike';
WITH CTE(ProductDescriptionID, position)
AS (
SELECT ProductDescriptionID, CHARINDEX(@searchValue, Description)
FROM [Production].[ProductDescription]
UNION ALL
SELECT a.ProductDescriptionID, CHARINDEX(@searchValue, a.Description, CTE.position + 1)
FROM [Production].[ProductDescription] a
INNER JOIN CTE ON A.ProductDescriptionID = cte.ProductDescriptionID
WHERE CHARINDEX(@searchValue, a.Description, CTE.position + 1) > 0
)
SELECT *
FROM CTE
WHERE position > 0
ORDER BY 1,2;
The highlighted items below are the records where there is more than 1 occurrence of the word we are searching for.
Count all Occurrences of a String in a String Example
Here is a similar example, but this query will provide the record ID along with how many times the search word appears in the description.
DECLARE @searchValue nvarchar(20) = 'bike';
WITH CTE(ProductDescriptionID, position)
AS (
SELECT ProductDescriptionID, CHARINDEX(@searchValue, Description)
FROM [Production].[ProductDescription]
UNION ALL
SELECT a.ProductDescriptionID, CHARINDEX(@searchValue, a.Description, CTE.position + 1)
FROM [Production].[ProductDescription] a
INNER JOIN CTE ON A.ProductDescriptionID = cte.ProductDescriptionID
WHERE CHARINDEX(@searchValue, a.Description, CTE.position + 1) > 0
)
SELECT ProductDescriptionID, count(position) as Occurrence
FROM CTE
WHERE position > 0
GROUP BY ProductDescriptionID
ORDER BY 1,2;
