SQLTeam.com | Weblogs | Forums

How to update status to not match character in case of signature key not have stars *?

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

I am starting to think you might be a bot. habibi you are going to have to start showing some initiative and some hard work.

hi

i tried to do this !!! this uses string split 2017 version

.. you can use Delimited 8K by Jeff if you cant use string_split

please click arrow to the left for DROP CREATE data Script
drop table ref
go 

Create table Ref
(
SignatureKey  nvarchar(50),
GroupId int,
PortionKey nvarchar(50),
Status nvarchar(100)
)
go 

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)
go
; with cte as 
(
select 
      ROW_NUMBER() over(partition by Signaturekey order by Signaturekey) as rn 
	, *  
from 
    ref 
       cross apply 
	string_split(SignatureKey,'$')
)
select 
     case when PortionKey <> value then 'Not Match Charachters' end 
   , * 
from 
   cte 
where 
   rn = GroupId and PortionKey <> value
order by 
  rn 

image
image

thanks