I work on SQL server 2012 I face issue I need when signature key group id have starts only then not compare
value with portion key as line or row 3 and 4
meaning if signature key group id have stars only then not update status to not match characters .
if group id is starts the not compare
if group id is have character or value as f then compare it with portion key based on group id
cannot compare star * with character as d this actually i need to do
when signature key group id have starts only then not compare with portion key.
to more clear suppose i have signature key is
SignatureKey GroupId PortionKey Status
*$****$*$***$***$**$* 2 g NULL
as above group id 2 represent 4 starts then not update status to not match character because it not have any character to compare on group id 2 but suppose as below this case
SignatureKey GroupId PortionKey Status
*$d$*$***$***$**$* 2 g Not Match charachter
Create table #Ref
(
SignatureKey nvarchar(50),
GroupId int,
PortionKey nvarchar(50),
Status nvarchar(100)
)
insert into #Ref(SignatureKey,GroupId,PortionKey,status)
values
('*$*$C$***$**$**$*',3,'s',NUll),
('*$*$*$FG$*$**$*',4,'F',NUll),
('*$*$*$***$*$D$*',6,'D',NUll),
('*$*$*$***$***$**$*',2,'g',NUll),
('*$**$*$***$L$**$*',5,'f',NUll)
update r set r.Status='Not Match Charachters'
from #Ref r
cross apply
dbo.Split(r.SignatureKey,'$') f where CAST (r.GroupId AS INT) = f.Id and r.PortionKey <> f.Data
Expected result
SignatureKey | GroupId | PortionKey | Status |
---|---|---|---|
$C**$ | 3 | s | Not Match Charachters |
*FG** | 4 | F | Not Match Charachters |
******** | 2 | g | NULL |
******* | 5 | f | NULL |