using @JeffModen DelimitedSplit8K
use sqlteam
go
create table #Ref
(
SignatureKey nvarchar(50),
GroupId int,
PortionKey nvarchar(50),
Status nvarchar(100)
)
insert into #Ref(SignatureKey,GroupId,PortionKey,status)
values
('*$*$**$***$**$**$*',3,'s',NUll),
('*$*$*$***$*$**$*',4,'s2',NUll),
('*$*$*$***$*$**$*',6,'shd',NUll),
('*$**$*$***$***$**$*',2,'g',NUll),
('*$**$*$***$**$**$*',5,'f',NUll),
('*$*$*******$*$**$**$*',7,'f',NUll) --test extra to see accuracy 7
;with jazz
as
(
select *
From #Ref r
cross apply DelimitedSplit8K(r.SignatureKey, '$')
--this function from Sir Jeff Moden
--https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function
)
select SignatureKey ,GroupId, PortionKey,
case
when len(item) = GroupId then 'Correct Result'
else 'Wrong length'
end as Status
from jazz
where ItemNumber = 3
drop table #Ref