Click here to Skip to main content
13,513,747 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


13 bookmarked
Posted 27 Apr 2010

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

, 25 Jun 2010
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

 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'

-- Create the actual function

Counts the number of times @SearchString appears in @Input.
ALTER FUNCTION dbo.com_CountString(@Input nVarChar(max), @SearchString nVarChar(1000))
    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
        IF SUBSTRING(@Input, @Index, @SearchLength) = @SearchString
            SET @Count = @Count + 1
            SET @Index = @Index + @SearchLength
            SET @Index = @Index + 1

    RETURN @Count

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


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


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.

You may also be interested in...


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    Praise Praise    Rant Rant    Admin Admin   

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

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