Counting and Sorting Occurrences of a String in SQL

This particular function will count the number of occurrences of a string within a SQL column. I can’t really take credit for this particular SQL function, I actually found it in an obscure post on SQL-Server-Performance.com. But I thought the usefulness of it so great that I’d post and share it.

The advantage of this would be that you can sort the columns before hand with a DESC or ASC with a SQL statement based on the number of occurrences of a string instead of having to do it with some kind of logic on the client side. Doing it natively in SQL also means that it will be very fast.

To use this function with Microsoft SQL server, go to your database in SQL Server Enterprise Manager, browse to “User Defined Functions”, right click and choose “New User Defined Function…”. Paste the function below into the window and click the “OK” button.

CREATE FUNCTION CharCount(@Find varchar(8000) , @SourceText varchar(8000) )
RETURNS int AS
BEGIN

if (@Find is null) or (@Find = '') or (@SourceText is null) or (@SourceText = '')
return 0


declare @len int
declare @pos int
declare @cnt int

--Break the string into gramSize chunks
set @len = len(@SourceText)
set @pos = 1
set @cnt = 0

--Add all the grams for this record
while charindex(@Find, @SourceText, @pos) > 0
begin
set @cnt = @cnt + 1
set @pos = charindex(@Find, @SourceText, @pos) + len(@Find)
end
return @cnt

END

You’ll have a new function defined there which you can call with a SQL statement that looks something like this:

SELECT dbo.CharCount('SearchTerm', description) as score, description FROM tablename WHERE dbo.CharCount('SearchTerm', description) > 0 ORDER BY score DESC")

This particular SQL statement checks for the number of occurrences of “SearchTerm” within the column “description” and then returns that number as column name “score”. The search results are sorted by their “score” in descending order.

Obviously you could use this to rank search terms and use it for searching. This happens to be what I use it for. But I’m sure there are plenty of other creative uses.

Leave a Comment