ahmed elbarbary

ahmed elbarbary

  • 1.1k
  • 1k
  • 50.9k

can't update status to match Characters where signature key value is

Sep 7 2020 10:30 PM
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
  1. Signature Key Group Id Portion Key Status  
  2. *$*$**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.
  1. Create table #Ref  
  2. (  
  3. SignatureKey nvarchar(50),  
  4. GroupId int,  
  5. PortionKey nvarchar(50),  
  6. Status nvarchar(100)  
  7. )  
  8. insert into #Ref(SignatureKey,GroupId,PortionKey,status)  
  9. values  
  10. ('*$*$C$***$**$**$*',3,'s',NUll),  
  11. ('*$*$*$FG$*$**$*',4,'F',NUll),  
  12. ('*$*$*$***$*$D$*',6,'D',NUll),  
  13. ('*$t**$*$***$***$**$*',2,'t12',NUll),  
  14. ('*$**$*$***$**t$**$*',5,'12t',NUll)  
  1. update r set r.Status='Not Match Charachters'  
  2. from #Ref r  
  3. cross apply  
  4. dbo.Split(r.SignatureKey,'$') f where CAST (r.GroupId AS INT) = f.Id and r.PortionKey <> f.Data  
  5. Expected Result :  
  1. Signature Key Group Id Portion Key Status  
  2. *$*$C$***$**$**$* 3 s Not Match Characters  
  3. *$*$*$FG$*$**$* 4 F Not Match Characters  
  4. *$*$*$***$*$D$* 6 D Not Match Characters  
  5. *$t**$*$***$***$**$* 2 t12 Match Characters  
  6. *$**$*$***$**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.

Brought to you by:

Answers (7)