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

Accepted Answer

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

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

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

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

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.

Share via

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

5 additional answers

Your answer