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.
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
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
Chris Maunder is the co-founder of
CodeProject and
ContentLab.com, and has been a prominent figure in the software development community for nearly 30 years. Hailing from Australia, Chris has a background in Mathematics, Astrophysics, Environmental Engineering and Defence Research. His programming endeavours span everything from FORTRAN on Super Computers, C++/MFC on Windows, through to to high-load .NET web applications and Python AI applications on everything from macOS to a Raspberry Pi. Chris is a full-stack developer who is as comfortable with SQL as he is with CSS.
In the late 1990s, he and his business partner David Cunningham recognized the need for a platform that would facilitate knowledge-sharing among developers, leading to the establishment of CodeProject.com in 1999. Chris's expertise in programming and his passion for fostering a collaborative environment have played a pivotal role in the success of CodeProject.com. Over the years, the website has grown into a vibrant community where programmers worldwide can connect, exchange ideas, and find solutions to coding challenges. Chris is a prolific contributor to the developer community through his articles and tutorials, and his latest passion project,
CodeProject.AI.
In addition to his work with CodeProject.com, Chris co-founded ContentLab and DeveloperMedia, two projects focussed on helping companies make their Software Projects a success. Chris's roles included Product Development, Content Creation, Client Satisfaction and Systems Automation.