I work on SQL server 2012 I face issue : I can't update status to match characters where signature key have stars * .
I need to update status to match characters where signature key have starts as example
- Signature Key Group Id Portion Key Status
- *$*$**s$***$**$**$* 3 12s Match Characters
group id 3 from signature key above is **s must equal portion key 12s so status must be Match Characters .
but if group id value is **f and portion key value is 15g then it will be Not Match Character status
because g not equal f.
- 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),
- ('*$t**$*$***$***$**$*',2,'t12',NUll),
- ('*$**$*$***$**t$**$*',5,'12t',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 :
- Signature Key Group Id Portion Key Status
- *$*$C$***$**$**$* 3 s Not Match Characters
- *$*$*$FG$*$**$* 4 F Not Match Characters
- *$*$*$***$*$D$* 6 D Not Match Characters
- *$t**$*$***$***$**$* 2 t12 Match Characters
- *$**$*$***$**t$**$* 5 12t Match Characters
what I need to say is status with be Match characters in case of signature key value equal to portion key
exactly as (c = c) or signature key have stars on group id so i will ignore starts * and compare
character with character as (*f = 1f) meaning if i have stars then ignore compare with character.