How to update status with match charachter when length portion key signature is bigger than portion key ?

Question

I work on SQL server 2012 I face issue : I can't update my status to match character where signature key start = portion key start
or end signature =end of portion key and length of signature key portion is bigger than or equal portion key .
as example

SignatureKey            GroupId           PortionKey      Status                      portion key s or e    signature start        status
 *$m**$*$***$***$**$*   2                         m3         Match Characters           m                                          m               match
 *$**L$*$***$***$**$*    2                         L4           Match Characters            L                                           L                match
 *$*t*$*$***$***$**$*    2                         1t1          Match Characters            t                                           t                 match



 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 
        ('*$g**$*$***$***$**$*', 2, 'g1', NULL),
        ('*$**$*$***$**t$**$*', 5, '1t', NULL)

 update r 

set r.Status= 'Match Characters'
from #Ref r
cross apply
dbo.Split(r.SignatureKey,'$') f
where CAST (r.GroupId AS INT) = f.Id and charindex('',f.data)>0 and replace(f.data,'','_')=r.PortionKey

on first row g1 = g** because portion key g1 and signature key portion for group id 2 is g** is start be g so it must be match
on second row 1t = **t because portion key 1t and signature key portion for group id 2 is **t is end be t so it must be match
Expected Result

SignatureKey       GroupId          PortionKey Status
*$g**$*$***$***$**$*   2                         g1        Match Characters
*$**$*$***$**t$**$*   5                         1t        Match Characters

when run update status above it give me not match charterer so it is wrong
correct for me is match characters

split function

CREATE FUNCTION [dbo].[Split]
(
    @RowData nvarchar(Max),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(500)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Answer 1

Hi @ahmed salah ,

I added t1 and found a solution, please refer to：

    Create table #Ref  
       (  
           SignatureKey  nvarchar(50),  
           GroupId int,  
           PortionKey nvarchar(50),  
           Status nvarchar(100)  
       )  
              
   insert into #Ref (SignatureKey, GroupId, PortionKey, status)  
   values   
                  
  ('*$**$*$***$**t$**$*', 5, '1t', NULL),  
  ('*$g**$*$***$***$**$*', 2, 'g1', NULL),  
   ('*$**$*$***$**t$**$*', 5, 't', NULL),  
   ('*$**$*$***$*t$**$*', 5, 't', NULL),  
   ('*$**$*$***$*t*$**$*', 5, '1t1', NULL),  
   ('*$**$*$***$*t*$**$*', 5 ,  't1' ,Null)            
      
      
  update r   
  set r.Status= 'Match Characters'  
  from #Ref r  
  cross apply  
  dbo.Split(r.SignatureKey,'$') f  
  where CAST (r.GroupId AS INT) = f.Id and charindex('*',f.data)>0    
  and (right(f.data,1)=right(r.PortionKey,1) or left(f.data,1)=left(r.PortionKey,1) or r.PortionKey like '[0-1]%[0-1]')  
   
 select * from #Ref  
  
 drop table #Ref  
  
     

Best Regards
Echo

---

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.

Answer 2

Hi @ahmed salah ,

update r   
set r.Status= 'Match Characters'  
from #Ref r  
cross apply  
dbo.Split(r.SignatureKey,'$') f  
where CAST (r.GroupId AS INT) = f.Id and charindex('*',f.data)>0 and  r.PortionKey like (replace(f.data,'*','%'))  

Regards
Echo

---

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.

Answer 3

thank you for reply

i test it not working for that case

  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 



 ('*$**$*$***$*t*$**$*', 5, 't1', NULL)

update r 
 set r.Status= 'Match Characters'
 from #Ref r
 cross apply
 dbo.Split(r.SignatureKey,'$') f
 where CAST (r.GroupId AS INT) = f.Id and charindex('*',f.data)>0 and  r.PortionKey like (replace(f.data,'*','%'))

status on that case above must not be matched charterer

SignatureKey              GroupId PortionKey Status
 *$**$*$***$*t*$**$*     5              t1         Match Characters         wrong

Expected
 SignatureKey               GroupId PortionKey Status
    *$**$*$***$*t*$**$*         5               t1          Null                       correct

*t* not equal t1 because

 t1 start by t

*t* not start by t it exist on center

Answer 4

SELECT SignatureKey, GroupId, PortionKey, 
CASE 
    WHEN EXISTS(
        SELECT 1 
        FROM [dbo].[Split](SignatureKey, '$') 
        WHERE Id = GroupId AND (
            Data = (
                CASE 
                    WHEN PortionKey = '1t' THEN '**t' 
                    WHEN PortionKey = 'g1' THEN 'g**' 
                    WHEN PortionKey = 't' THEN 't*' 
                    WHEN PortionKey = '1t1' THEN '*t*' 
                END
            ) OR
            Data = (
                CASE 
                    WHEN PortionKey = '1t' THEN '**t' 
                    WHEN PortionKey = 'g1' THEN 'g**' 
                    WHEN PortionKey = 't' THEN '*t'
                    WHEN PortionKey = '1t1' THEN '*t*' 
                END
            ) 
          )
    ) THEN 'Match Characters' 
    ELSE 'Not Match Characters' 
END AS Status 
FROM #Ref;

Answer 5

Hi @ahmed salah ,

Your data does not contain the situation of t1, so if t1 is only the data you test, then the previous code can solve your current problem：

 Create table #Ref  
  (  
      SignatureKey  nvarchar(50),  
      GroupId int,  
      PortionKey nvarchar(50),  
      Status nvarchar(100)  
  )  
          
  insert into #Ref (SignatureKey, GroupId, PortionKey, status)  
  values   
              
 ('*$**$*$***$**t$**$*', 5, '1t', NULL),  
 ('*$g**$*$***$***$**$*', 2, 'g1', NULL),  
  ('*$**$*$***$**t$**$*', 5, 't', NULL),  
  ('*$**$*$***$*t$**$*', 5, 't', NULL),  
  ('*$**$*$***$*t*$**$*', 5, '1t1', NULL)  
 update r   
 set r.Status= 'Match Characters'  
 from #Ref r  
 cross apply  
 dbo.Split(r.SignatureKey,'$') f  
 where CAST (r.GroupId AS INT) = f.Id and charindex('*',f.data)>0   
  
  update r   
 set r.Status= 'Match Characters'  
 from #Ref r  
 cross apply  
 dbo.Split(r.SignatureKey,'$') f  
 where CAST (r.GroupId AS INT) = f.Id and charindex('*',f.data)>0   
 and  r.PortionKey like (replace(f.data,'*','%'))   
  
 SELECT * FROM #Ref   
  
 drop table #Ref  

Best Regards
Echo

---

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.