SQL Server CHARINDEX Function


By:

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')
charindex example 1

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)
charindex example 2

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.

sql charindex find multiple times

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;
charindex example 4

Related Articles


Last Update: 11/2/2021




Comments For This Article





download














get free sql tips
agree to terms