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?


#3

hi

i know this topic is 12 months ago

i have tried recursive cte approach
"Seniors" bitsmed and Scott Pletcher do not suggest
recursive cte due to performance issues

Because this is a simple string I have done it
using recursive cte

:slight_smile:
:slight_smile:

drop create data
use tempdb 
go 

drop table #t 
go

create table #t 
(
string varchar(100) 
)
go 

insert into #t select '2PB 3A 2PB GER USBCP HDMI 1MLP 1PWWW'
go
SQL ..
;WITH rec_cte 
     AS (SELECT Stuff(string, Patindex('%[0-9]P%', string), 2, '') AS ok, 
                1                                                  AS grp 
         FROM   #t a 
         UNION ALL 
         SELECT Stuff(ok, Patindex('%[0-9]P%', ok), 2, ''), 
                1 + grp 
         FROM   rec_cte b 
         WHERE  Patindex('%[0-9]P%', ok) <> 0) 
SELECT Max(grp) 
FROM   rec_cte 

go
Result

image