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

thank you very much for support