|
Wednesday, 21 December 2011 01:21 |
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
And finally 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
 Read more: |