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, DeveloperMedia and ContentLab, 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. While at CodeProject, Chris' roles included Architecture and coding, Product Development, Content Creation, Community Growth, Client Satisfaction and Systems Automation, and many, many sales meetings. All while keeping his sense of humour.