Platinum Partner

Count Total Number Of Occurrences Of String Inside Another String In SQL

I had a need to count the number of times a certain string appeared within a column in a SQL table. I came up with this simple function that may be of use to others.


-- Setup: Create a blank function if none exists. This allows us to 
-- rerun this single script each time we modify this function

IF NOT EXISTS (SELECT * FROM sys.objects
 WHERE object_id = OBJECT_ID(N'dbo.com_CountString')
 AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
EXEC dbo.sp_executesql @statement = N'create function dbo.com_CountString() RETURNS INT AS BEGIN RETURN '''' END'
go
 
-- Create the actual function

/*====================================================================================
Counts the number of times @SearchString appears in @Input.
====================================================================================*/
ALTER FUNCTION dbo.com_CountString(@Input nVarChar(max), @SearchString nVarChar(1000))
RETURNS INT
BEGIN
    DECLARE @Count INT, @Index INT, @InputLength INT, @SearchLength INT
    DECLARE @SampleString INT
 
    if @Input is null or @SearchString is null
        return 0
 
    SET @Count = 0
    SET @Index = 1
    SET @InputLength  = LEN(@Input)
    SET @SearchLength = LEN(@SearchString)
 
    if @InputLength = 0 or @SearchLength = 0 or @SearchLength > @InputLength
        return 0
 
    WHILE @Index <= @InputLength - @SearchLength + 1
    BEGIN
        IF SUBSTRING(@Input, @Index, @SearchLength) = @SearchString
        BEGIN
            SET @Count = @Count + 1
            SET @Index = @Index + @SearchLength
        END
        ELSE
            SET @Index = @Index + 1
    END
 
    RETURN @Count
END
GO


And finally The function can be called:

SELECT dbo.com_CountString('This is a string', 'is')
 
SELECT dbo.com_CountString(MyTable.MyColumn, 'search string')
FROM  MyTable
WHERE MyTable.MyKey = @Key


Spring Framework
{{ tag }}, {{tag}},

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks
Tweet

{{parent.nComments}}