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

Question

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 charachters

if group id is starts the not compare
if group id is have charachter or value as f then compare it with portion key based on group id
cannot compare star * with charachter 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   

on case above i will compare because group id 2 have value d so i will update status to not match character

because on group id 2 portion key g not equal d .

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), --1
('$$$FG$$$',4,'F',NUll), ---2
('$$$$$$',2,'g',NUll), --3
('$$$$$$',5,'f',NUll) --4

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

]1

Answer 1

Try this approach too:

create table #Ref
(
    SignatureKey nvarchar(50),
    GroupId int,
    PortionKey nvarchar(50),
    [Status] nvarchar(100)
)

insert into #Ref values
    ('*$*$C$***$**$**$*', 3, 's', NUll), --1
    ('*$*$*$FG$*$**$*', 4, 'F', NUll), ---2
    ('*$*$*$***$***$**$*', 2, 'g', NUll), --3
    ('*$**$*$***$*$**$*', 5, 'f', NUll) --4

select * from #Ref

;
with Q1 as
(
    select *, 
        replace(replace(replace(SignatureKey, '**', '*?'), '?*', '?'), '?', '') as sk
    from #Ref
),
Q2 as
(
    select *,
        replicate('%$', GroupId - 1) + PortionKey + replicate('$%', 7 - GroupId) as p1,
        replicate('%$', GroupId - 1) + '*' + replicate('$%', 7 - GroupId) as p2
    from Q1
),
Q3 as
(
    select *,
        case when sk like p1 or sk like p2 then null else 'Not Match Characters' end as s
    from Q2
)
update Q3
set [Status] = s

select * from #Ref

If there are some unconsidered cases, give details.

Answer 2

Hi @ahmed salah ,

Try code as next:

 --create function  
    CREATE FUNCTION dbo.GetSplitString  
    (  
       @List       VARCHAR(MAX),  
      
       @Delimiter  VARCHAR(255),  
      
       @ElementNumber int  
    )  
    RETURNS VARCHAR(4000)  
    AS  
    BEGIN  
       DECLARE @result varchar(4000)      
       DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,  
                              Item VARCHAR(4000)  
                             )   
       DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);    
       WITH a AS  
       (  
           SELECT  
               [start] = 1,  
               [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter,  
                           @List, @ld), 0), @ll),  
               [value] = SUBSTRING(@List, 1,  
                         COALESCE(NULLIF(CHARINDEX(@Delimiter,  
                           @List, @ld), 0), @ll) - 1)  
           UNION ALL  
           SELECT  
               [start] = CONVERT(INT, [end]) + @ld,  
               [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter,  
                           @List, [end] + @ld), 0), @ll),  
               [value] = SUBSTRING(@List, [end] + @ld,  
                         COALESCE(NULLIF(CHARINDEX(@Delimiter,  
                           @List, [end] + @ld), 0), @ll)-[end]-@ld)  
           FROM a  
           WHERE [end] < @ll  
       )  
       INSERT @Items SELECT [value]  
       FROM a  
       WHERE LEN([value]) > 0  
       OPTION (MAXRECURSION 0);  
       SELECT @result=Item  
       FROM @Items  
       WHERE position=@ElementNumber  
       RETURN @result;  
    END  
    GO  

--create  table  
use test  
go  
  
drop table Ref  
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), --1  
('*$*$*$FG$*$**$*',4,'F',NUll), ---2  
('*$*$*$***$***$**$*',2,'g',NUll), --3  
('*$**$*$***$*$**$*',5,'f',NUll) --4  
  
--test table  
select * from Ref;  
  
--test function  
SELECT  dbo.GetSplitString('*$*$C$***$**$**$*','$',3);  
  
--update table  
UPDATE Ref  
SET Status =  CASE    
                        WHEN dbo.GetSplitString(SignatureKey,'$',GroupId) = PortionKey THEN 'Match'   
 WHEN dbo.GetSplitString(SignatureKey,'$',GroupId) not like '%[^A-Z]%' then 'Not Match Charachters'  
                        ELSE 'NULL'  
                    END  
  
  
--test after update  
select * from Ref;  

BR，
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.