SQLTeam.com | Weblogs | Forums

How to update status with match character in case of length portion key signature is bigger than portion key and position equal position portion key?

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      signature    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

hi again ahmed

; with cte as 
(
select ROW_NUMBER() over(partition by Signaturekey order by Signaturekey) as rn 	, replace(value,'*','') as str	,* from  ref 
           cross apply 
	   string_split(SignatureKey,'$')
)
select 
      Signaturekey 
   ,  GroupId
   ,  PortionKey
   ,  'Match Characters' as Status 
from 
   cte 
where 
   rn = GroupId 
     and 
  PortionKey like '%'+str+'%'

image
image

hi ahmed

no update for this
no update for my earlier post help also

Whats happening !!!

thank you for reply
it work for all cases above remain one case not work for it
suppose i have letter character on center
it must not update status for it as Matched Character
*t* not equal t1 because

 t1 start by t

*t* not start by t it exist on center

so status must not be Matched Character

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)

		 ; with cte as 
(
select ROW_NUMBER() over(partition by Signaturekey order by Signaturekey) as rn 	, replace(value,'*','') as str	,* from  #ref 
           cross apply 
	   string_split(SignatureKey,'$')
)
select 
      Signaturekey 
   ,  GroupId
   ,  PortionKey
   ,  'Match Characters' as Status 
from 
   cte 
where 
   rn = GroupId 
     and 
  PortionKey like '%'+str+'%'

|Signaturekey|GroupId|PortionKey|Status|
|---|---|---|---|
|*$**$*$***$*t*$**$*|5|t1|Match Characters|   wrong

hi

the center i have letter character ..you said ..

how will the data be
Always 1 letter between *

Examples ..
image

If yes Tell me .. I can change the SQL to check for this also

yes always be like below :

   *t*
    **t*

hi

please see my new SQL ..

; with cte as 
(
select ROW_NUMBER() over(partition by Signaturekey order by Signaturekey) as rn 	, replace(value,'*','') as str	,* from  ref 
           cross apply 
	   string_split(SignatureKey,'$')	   
)
select 'SQL Output',
      Signaturekey 
   ,  GroupId
   ,  PortionKey
   ,  'Match Characters' as Status 
from 
   cte 
where 
   rn = GroupId 
     and 
  PortionKey like '%'+str+'%'
     and 
  charindex(str,value) = charindex(str,portionkey) 

image
image

thank you for reply last case if possible

 Create table #Ref
  (
      SignatureKey  nvarchar(50),
      GroupId int,
      PortionKey nvarchar(50),
      Status nvarchar(100)
  )
        
  insert into #Ref (SignatureKey, GroupId, PortionKey, status)
  values 
      
		 ('*$**$*$***$*t$**$*', 5, 't', NULL),
		 ('*$**$*$***$t*$**$*', 5, 't', NULL)

		 ; with cte as 
(
select ROW_NUMBER() over(partition by Signaturekey order by Signaturekey) as rn 	, replace(value,'*','') as str	,* from  #ref 
           cross apply 
	   string_split(SignatureKey,'$')	   
)
select 'SQL Output',
      Signaturekey 
   ,  GroupId
   ,  PortionKey
   ,  'Match Characters' as Status 
from 
   cte 
where 
   rn = GroupId 
     and 
  PortionKey  like '%'+str+'%'
     and 
  charindex(str,value) =charindex(str,portionkey) 

it must return also first row
meaning below row must be returned as matched

|(No column name)|Signaturekey|GroupId|PortionKey|Status|
|---|---|---|---|---|
|SQL Output|*$**$*$***$*t$**$*|5|t|Match Characters|

if one select statement for these two rows only i can accept .
but correct it must return two rows to be correct

*t and t is same t is end
t* and t is same t is start

you can do select statement for these as separate case

if you can make select statement to return two rows above as matched without modify first statement

Without modify first statement

MEANS

sorry what I mean how to modify query above on last reply
to return this row also :slight_smile:

 SignatureKey	       GroupId	PortionKey	
*$**$*$***$*t$**$*	     5	             t	  

as matched characters
because two rows must return .
it return only one row

meaning i need to modify this query below to return row inside as matched characters :slight_smile:

 Create table #Ref
  (
      SignatureKey  nvarchar(50),
      GroupId int,
      PortionKey nvarchar(50),
      Status nvarchar(100)
  )
        
  insert into #Ref (SignatureKey, GroupId, PortionKey, status)
  values 
      
		 ('*$**$*$***$*t$**$*', 5, 't', NULL)
		



		 ; with cte as 
(
select ROW_NUMBER() over(partition by Signaturekey order by Signaturekey) as rn 	, replace(value,'*','') as str	,* from  #ref 
           cross apply 
	   string_split(SignatureKey,'$')	   
)
select 'SQL Output',
      Signaturekey 
   ,  GroupId
   ,  PortionKey
   ,  'Match Characters' as Status 
from 
   cte 
where 
   rn = GroupId 
     and 
  PortionKey  like '%'+str+'%'
     and 
  charindex(str,value) =charindex(str,portionkey)

it must return row because
*t = t
*t end with t
t is end
so expected result is query above returned row inside as matched characters

hi

here is modified SQL .. hope this works for all your cases

; with cte as 
(
select ROW_NUMBER() over(partition by Signaturekey order by Signaturekey) as rn 	, replace(value,'*','') as str	,* from  ref 
           cross apply 
	   string_split(SignatureKey,'$')	   
)
select 'SQL Output',
      Signaturekey 
   ,  GroupId
   ,  PortionKey
   ,  'Match Characters' as Status 
   , value
   , portionkey
from 
   cte 
where 
   rn = GroupId 
     and 
  PortionKey  like '%'+str+'%'
    and 
  (    charindex(str,value) =charindex(str,portionkey) 
         or 
	   charindex(str,value) =charindex(str,portionkey)+1
  ) 

image

hi ahmed

did my last solution work for you !!

thank you very much