DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

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

12.21.2011
| 3699 views |
  • submit to reddit
        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

<a href="http://www.java-forums.org/blogs/spring-framework/"><strong>Spring Framework</strong></a>