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.
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
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