Counting occurrences of a substring within a string

re: Counting occurrences of a substring within a string

beckham — Mon, 07 May 2007 04:59:56 GMT

Good one..

re: Counting occurrences of a substring within a string

Jereme Guenther — Tue, 17 Jun 2008 15:12:36 GMT

I am looking for this functionality, however I would prefer to do it without a Numbers table.

My need is that of a search engine sproc. I need to be able to weight the returned records and one of the items in the weighting system is to check how many times the search pattern occurs in the record.

re: Counting occurrences of a substring within a string

Pickles — Wed, 16 Jul 2008 22:28:08 GMT

I need this too. I have a column that basically contains sql and I need to pull out field_ids from it, but each row has a different number of field_ids in the sql. Is there a different(better) solution?

re: Counting occurrences of a substring within a string

Adam Machanic — Wed, 23 Jul 2008 18:30:08 GMT

I don't know why you would want to do it without the numbers table. It makes the solution an order of magnitude faster.

re: Counting occurrences of a substring within a string

Craig Hathaway — Mon, 23 Feb 2009 02:06:48 GMT

Hi Adam, I have tried this code but even the included examples do not work!

SELECT dbo.GetSubstringCount('We like the autos, the autos that go boom.', 'auto', '')

-- returns 0 <-- should be 2

SELECT dbo.GetSubstringCount('Autos are fun. I like to drive my auto.', 'auto', '')

-- returns 0 <-- should be 2

SELECT dbo.GetSubstringCount('Autos are fun. I like to drive my auto.', 'auto', '%[a-z]%')

-- returns 1 <-- should be 1

Is there a SQL setting / version dependency that O could be missing?

Craig

re: Counting occurrences of a substring within a string

Adam Machanic — Mon, 23 Feb 2009 16:18:51 GMT

Hi Craig,

Thanks for pointing this out. It is indeed a version issue. I wrote this back in the bad old days of SQL Server 2000, and apparently there was a change to the way PATINDEX works between 2000 and 2005. I just tested:

SELECT PATINDEX('', ' ')

SQL Server 2000 returns 0, whereas SQL Server 2005 returns 1. This is breaking the third predicate in the WHERE clause within the function, which checks to see if the target string is prepended by anything that matches the input pattern.

I'll have to think about how to fix this, but as a temporary workaround if you don't want to use exact-match mode, you could pass in some character that you know can't possibly exist in the target string:

SELECT dbo.GetSubstringCount('We like the autos, the autos that go boom.', 'auto', CHAR(255))

--Returns 2, even in SQL Server 2005 or 2008

In the meantime, I'm wondering if this "new" behavior makes sense? I'm not sure, but I'm leaning towards SQL Server 2000's answer. An empty pattern shouldn't, in my opinion, match on anything at all...

re: Counting occurrences of a substring within a string

Funmarkaz — Tue, 06 Oct 2009 05:25:05 GMT

Great work dude!

Can i use it on MyISAM?

re: Counting occurrences of a substring within a string

Adam Machanic — Tue, 06 Oct 2009 18:27:33 GMT

Funmarkaz, yes, it should work with a bit of modification; MySQL's CREATE FUNCTION syntax isn't quite the same as SQL Server's.

re: Counting occurrences of a substring within a string

Mike Schafer — Tue, 29 Dec 2009 17:51:20 GMT

I found this post because I was searching for a function for a project where I need to find out how many delimiters exist in a string. This implementation does not require a numbers table or anything other than the function itself. There is no "additional bonus" feature in this version but it will count occurences without any additional db objects. Happy Querying!

CREATE FUNCTION dbo.GetSubStringCount (

@InputString NVARCHAR(4000),

@SearchString VARCHAR(255)

)RETURNS INT WITH SCHEMABINDING AS

BEGIN

DECLARE @occurences AS BIGINT

,@position AS BIGINT

SET @occurences = 0

SET @position = 0

WHILE @position < LEN(@InputString)

BEGIN

IF CHARINDEX(@SearchString, @InputString, @position) > 0

BEGIN

SET @occurences = @occurences + 1

SET @position = CHARINDEX(@SearchString, @InputString, @position)

END

SET @position = @position+1

END

RETURN @occurences

END

re: Counting occurrences of a substring within a string

Adam Machanic — Wed, 30 Dec 2009 16:47:53 GMT

Mike,

Thanks for sharing. A simpler and more efficient way to solve the problem (if you don't want the "bonus" feature) is to do:

SELECT LEN(@InputString) - REPLACE(@InputString, @SearchString, '')

I suspect that the numbers table will provide better performance than a WHILE loop, and both will be less efficient than the above solution, but I'll leave that testing as an exercise for anyone interested in taking this a bit further.

Even better would be to inline the Numbers table version (search my blog for my post on that topic), and a SQLCLR solution would probably be fastest of all. I would personally definitely keep the "bonus" around as it's been quite useful in a few projects I've worked on.

re: Counting occurrences of a substring within a string

Brian Lewis — Wed, 28 Apr 2010 20:57:05 GMT

My reason for wanting this functionality: to count line breaks in sys.syscomments in order to measure how many lines of T-SQL there are in the project. The bonus feature will allow blank lines to be excluded from the count.

re: Counting occurrences of a substring within a string

Adam Machanic — Sat, 01 May 2010 15:49:25 GMT

Okay, the 2005/2008 bug is fixed.

re: Counting occurrences of a substring within a string

Jason — Fri, 10 Jun 2011 20:47:31 GMT

Adam, thanks for posting this. Very handy use of the numbers table.

I'm troubleshooting a system that is experiencing tempdb meta-data contention and trying to identify queries that are creating temp objects within ad-hoc sql statements. I have traces of this activity and am using this to identify the 'worst offenders'. Initially I was just counting each query where TextData was like '%table%' until I found that some batches created 10-15 table variables, so I needed a way to count them.

Works great. thx.

re: Counting occurrences of a substring within a string

Ron — Tue, 19 Feb 2013 13:35:21 GMT

Adam,

Is there a modification I can make to get:

SELECT dbo.GetSubstringCount('ROUTINE MEDICAL EXAMINATION', 'NORMAL ROUTINE HISTORY AND PHYSICAL', '%[a-z]%')

to return 1 (ROUTINE)

and

SELECT dbo.GetSubstringCount('REFLUX, ESOPHAGEAL', 'ESOPHAGEAL REFLUX', '%[a-z]%')

to return 2 (ESOPHAGEAL & REFLUX)

re: Counting occurrences of a substring within a string

Adam Machanic — Tue, 19 Feb 2013 14:50:51 GMT

Ron: Sure, it's doable, but it would be a completely different function. What you want to do is split both strings on any non-alpha character, then intersect the results. You can search my blog for a string splitter (the CLR version would probably be best), and then just use the INTERSECT operator to get your final answer.

--Adam