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

ahmed salah 3,126 Reputation points
2020-09-09T07:21:25.3+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,487 questions
No comments
1 vote

Accepted answer
  1. EchoLiu-MSFT 14,416 Reputation points
    2020-09-15T06:54:46.37+00:00

    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  
      
         
    

    24785-image.png

    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.

    No comments

5 additional answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,416 Reputation points
    2020-09-09T08:48:13.487+00:00

    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.

    No comments

  2. ahmed salah 3,126 Reputation points
    2020-09-09T09:11:24.16+00:00

    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
    

  3. Guoxiong 7,681 Reputation points
    2020-09-13T02:32:43.097+00:00
    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;
    
    No comments

  4. EchoLiu-MSFT 14,416 Reputation points
    2020-09-14T13:02:25.99+00:00

    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  
    

    24512-image.png

    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.

    No comments