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'
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
SET @Count = 0
SET @Index = 1
SET @InputLength = LEN(@Input)
SET @SearchLength = LEN(@SearchString)
if @InputLength = 0 or @SearchLength = 0 or @SearchLength > @InputLength
WHILE @Index <= @InputLength - @SearchLength + 1
IF SUBSTRING(@Input, @Index, @SearchLength) = @SearchString
SET @Count = @Count + 1
SET @Index = @Index + @SearchLength
SET @Index = @Index + 1
The function can be called:
SELECT dbo.com_CountString('This is a string', 'is')
SELECT dbo.com_CountString(MyTable.MyColumn, 'search string')
WHERE MyTable.MyKey = @Key
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.