Count number of times substring occurs in a column

Here is a tip to show how to count number of times a substring occurs in a field with simple Transact SQL.

This functionality is pretty common in languages like VB, but in SQL there#%92s no obvious solution. CharIndex will show that a substring exists, but not how many times.

This functionality can be very valuable when trying to deal with de-normalized data, such as when you have comma-delimited values inside a field.

Here#%92s what I came up with: use the replace function to return a string that has an extra space in it for every occurrence of the substring, then compare the length of the new string to the original. It should be one character longer for each occurrence.

In the example below I include a count of the substring (a delimiting comma):, as well as the values. Your query might be cleaner if nulls aren#%92t allowed.

CREATE TABLE #StringTest (ID int, StringField varchar(50))

INSERT INTO #StringTest VALUES(1, null)
INSERT INTO #StringTest VALUES(2, '')
INSERT INTO #StringTest VALUES(3, '1')
INSERT INTO #StringTest VALUES(4, '1,2')
INSERT INTO #StringTest VALUES(5, '1, 2, 3')
INSERT INTO #StringTest VALUES(6, '1, 2, 3, 4')

DECLARE
@SearchString varchar(50)

SET @SearchString = ','

SELECT
ID,
StringField,
len(replace(isnull(StringField, ''), @SearchString, @SearchString + ' ')) - len(isnull(StringField, '')) as DelimeterCount,
len(replace(isnull(StringField, ''), @SearchString, @SearchString + ' ')) - len(isnull(StringField, '')) + case when ltrim(isnull(StringField, '')) <> '' then 1 else 0 end as ValueCount
FROM #StringTest

DROP TABLE #StringTest


With Dynamic SQL, this could easily be changed into a stored procedure to deal with any table, any fieldname.


Tip provided by Scott Thompson, who is certified in SQL Server and Delphi, and has worked as a software developer and DBA in the insurance, financial and market research industries.

Here is a function you can use like the CharIndex() function but it instead it counts the occurances of the first string in the second one.



-- This function will looks for the number of occurances of @Find in @SourceText
-- If either parameter is null or blank, returns 0

CREATE FUNCTION CharCount(@Find varchar(8000) , @SourceText varchar(8000) )
RETURNS int AS
BEGIN

if (@Find is null) or (@Find = '') or (@SourceText is null) or (@SourceText = '')
return 0


declare @len int
declare @pos int
declare @cnt int

--Break the string into gramSize chunks
set @len = len(@SourceText)
set @pos = 1
set @cnt = 0

--Add all the grams for this record
while charindex(@Find, @SourceText, @pos) > 0
begin
set @cnt = @cnt + 1
set @pos = charindex(@Find, @SourceText, @pos) + len(@Find)
end
return @cnt

END