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.

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

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