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+'%'
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 ..
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)
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 
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
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
)
hi ahmed
did my last solution work for you !!