Click here to Skip to main content
11,505,005 members (68,017 online)
Click here to Skip to main content

Tagged as

Counting the number of occurrences of one string inside another in SQL

, 25 Jun 2010 CPOL 22.2K 12
Rate this:
Please Sign up or sign in to vote.
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
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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Chris Maunder
Founder CodeProject
Canada Canada
Chris is the Co-founder, Administrator, Architect, Chief Editor and Shameless Hack who wrote and runs The Code Project. He's been programming since 1988 while pretending to be, in various guises, an astrophysicist, mathematician, physicist, hydrologist, geomorphologist, defence intelligence researcher and then, when all that got a bit rough on the nerves, a web developer. He is a Microsoft Visual C++ MVP both globally and for Canada locally.

His programming experience includes C/C++, C#, SQL, MFC, ASP, ASP.NET, and far, far too much FORTRAN. He has worked on PocketPCs, AIX mainframes, Sun workstations, and a CRAY YMP C90 behemoth but finds notebooks take up less desk space.

He dodges, he weaves, and he never gets enough sleep. He is kind to small animals.

Chris was born and bred in Australia but splits his time between Toronto and Melbourne, depending on the weather. For relaxation he is into road cycling, snowboarding, rock climbing, and storm chasing.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
Questionn Pin
Member 799655620-Feb-14 13:07
memberMember 799655620-Feb-14 13:07 
GeneralOne reason possibly for creating if not found as apposed to ... Pin
GeorgeM200922-Aug-11 12:33
memberGeorgeM200922-Aug-11 12:33 
GeneralI also just steer clear of sp_executesql, unless its absolut... Pin
Andrew Rissing30-Jun-10 7:53
memberAndrew Rissing30-Jun-10 7:53 
GeneralOh, I wasn't saying you could get around the exists check, y... Pin
Andrew Rissing30-Jun-10 7:47
memberAndrew Rissing30-Jun-10 7:47 
GeneralWe'd still have to check for the existence before dropping i... Pin
Chris Maunder30-Jun-10 6:45
adminChris Maunder30-Jun-10 6:45 
GeneralOut of curiousity, why not just drop the function at the sta... Pin
Andrew Rissing30-Jun-10 4:06
memberAndrew Rissing30-Jun-10 4:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 25 Jun 2010
Article Copyright 2010 by Chris Maunder
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid