Count # of times a pattern appears in a string 3

[johnv20]

Does anyone have a quick way of counting the number of times a pattern appears in a string e.g. I need to count the number of times the expression 'ab' appears in the string 'ab xxxxabxx ab'.

I know I can use patindex to get the first location & keep shortening the string everytime its found, however going through this for tables with a large number of rows is proving very time consuming.

 

[vongrunt]

Dirty way:

select blah blah, (len(myfield) - len(replace(myfield, 'ab', '')))/len('ab')
from myTable

Looks ugly, but I guess it is less resource intensive than looping or UDF.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

vongrunt.. VERY COOL piece of code...
Jim

 

[gmmastros]

vongrunt,

That is very clever. You replace the string with an empty string, and then determine how much smaller the original string got.

I would have gone with the UDF approach, but your solution is better.

Have a star just for thinking outside the box.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[johnv20]

Thanks vongrunt - that is excellent