SQLTeam.com | Weblogs | Forums

Count occurrences of text in a string starting with a number between 1-9

sql2008

#1

Hi

I have the following string of text

2PB 3A 2PB GER USBCP HDMI 1MLP WWW

I am looking for the best way of counting how many time [1-9]P occurs in the string.
I have tried using variations of LEN(a1.description)-LEN(REPLACE(a1.description, PATINDEX('[1-9]P%',Text), '')) which gets me close, but there are some descrepancies.

Any ideas?


#2

Is this only leading values, or within strings?

So presumably

2PB 3A 2PB GER USBCP HDMI 1MLP WWW

is 2 (2PB and 2PB but not 1MLP)

What about

2PB 3A 2PB GER USBCP HDMI 1MLP WWW X9PX

does X9PX count?

if not I would SPLIT on spaces, and then match leading '[1-9]P%'

Or perhaps you could add a leading space to the pattern - something like

LEN(' '+a1.description)-LEN(REPLACE(a1.description, PATINDEX(' [1-9]P%',' '+Text), ''))

Would it help to use a REPLACE string of 'x', rather than blank, so that the LEN DIFF was the number of occurrences?