September 8, 2020 at 3:36 am
I work on SQL server 2012 I face issue : I can't update status to match characters where signature key have stars * .
I need to update status to match characters where signature key have starts as example
Signature Key Group Id Portion Key Status
*$*$**s$***$**$**$* 3 12s Match Characters
group id 3 from signature key above is **s must equal portion key 12s so status must be Match Characters .
but if group id value is **f and portion key value is 15g then it will be Not Match Character status
because g not equal f.
Create table #Ref(SignatureKey nvarchar(50),GroupId int,PortionKey nvarchar(50),Status nvarchar(100))insert into #Ref(SignatureKey,GroupId,PortionKey,status)values('*$*$C$***$**$**$*',3,'s',NUll),('*$*$*$FG$*$**$*',4,'F',NUll),('*$*$*$***$*$D$*',6,'D',NUll),('*$t**$*$***$***$**$*',2,'t12',NUll),('*$**$*$***$**t$**$*',5,'12t',NUll)update r set r.Status='Not Match Charachters'from #Ref rcross applydbo.Split(r.SignatureKey,'$') f where CAST (r.GroupId AS INT) = f.Id and r.PortionKey <> f.DataExpected Result :
Signature Key Group Id Portion Key Status
*$*$C$***$**$**$* 3 s Not Match Characters
*$*$*$FG$*$**$* 4 F Not Match Characters
*$*$*$***$*$D$* 6 D Not Match Characters
*$t**$*$***$***$**$* 2 t12 Match Characters
*$**$*$***$**t$**$* 5 12t Match Characters
what I need to say is status with be Match characters in case of signature key value equal to portion key
exactly as (c = c) or signature key have stars on group id so i will ignore starts * and compare
character with character as (*f = 1f) meaning if i have stars then ignore compare with character.
September 8, 2020 at 1:53 pm
May be something like this.
(Search this site for DelimitedSplit8K id you do not already have it)
SELECTi.[Signature Key],i.[Group Id],i.Portion,
CASE WHEN i.Portion LIKE REPLACE(k.Item,'*','_') THEN '' ELSE 'Not ' END + 'Match Characters'
FROM(VALUES
('*$*$C$***$**$**$*', 3, 's'),
('*$*$*$FG$*$**$*', 4, 'F'),
('*$*$*$***$*$D$*', 6, 'D'),
('*$t**$*$***$***$**$*', 2, 't12'),
('*$**$*$***$**t$**$*', 5, '12t')
) i ([Signature Key],[Group Id],Portion)
CROSS APPLY master.dbo.DelimitedSplit8K([Signature Key],'$') k
WHERE k.ItemNumber = i.[Group Id];
Far away is close at hand in the images of elsewhere.
Anon.
September 8, 2020 at 2:43 pm
There was a slight addition needed to David Burrow's code above. Which is nicely done and way better than the (now Closed) Stack Overflow answer to the same question. It seems when there is not any matching done on number placeholders, then the query should return 'Not Match Characters' even though there is an exact match on characters (lol). From the OP's result the 3rd row should be 'Not Match Characters'. I added the extra condition to the CASE WHEN in the query.
SELECTi.[Signature Key], i.[Group Id], i.Portion,
CASE WHEN (i.Portion NOT LIKE '%[0-9]%') THEN 'Not ' else
case when (i.Portion LIKE rep.r_str) THEN ''
ELSE 'Not 'end END + 'Match Characters'
FROM(VALUES
('*$*$C$***$**$**$*', 3, 's'),
('*$*$*$FG$*$**$*', 4, 'F'),
('*$*$*$***$*$D$*', 6, 'D'),
('*$t**$*$***$***$**$*', 2, 't12'),
('*$**$*$***$**t$**$*', 5, '12t')
) i ([Signature Key],[Group Id],Portion)
CROSS APPLY dbo.DelimitedSplit8K([Signature Key],'$') k
cross apply (select REPLACE(k.Item,'*','_') r_str) rep
WHERE k.ItemNumber = i.[Group Id];
Output is now
Signature KeyGroup IdPortion(No column name)
*$*$C$***$**$**$*3sNot Match Characters
*$*$*$FG$*$**$* 4FNot Match Characters
*$*$*$***$*$D$* 6DNot Match Characters
*$t**$*$***$***$**$*2t12Match Characters
*$**$*$***$**t$**$*512tMatch Characters
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 8, 2020 at 4:15 pm
There was a slight addition needed to David Burrow's code above. Which is nicely done and way better than the (now Closed) Stack Overflow answer to the same question. It seems when there is not any matching done on number placeholders, then the query should return 'Not Match Characters' even though there is an exact match on characters (lol). From the OP's result the 3rd row should be 'Not Match Characters'. I added the extra condition to the CASE WHEN in the query.
Thanks Steve, I overlooked that bit 🙁
Thought is was strange when D=D is not a match unless only matching with presence of asterisks.
A slight tweak
SELECTi.[Signature Key],i.[Group Id],i.Portion,
CASE WHEN i.Portion LIKE REPLACE(k.Item,'*','_')
AND k.Item LIKE '%*%'
THEN '' ELSE 'Not ' END + 'Match Characters'
FROM(VALUES
('*$*$C$***$**$**$*', 3, 's'),
('*$*$*$FG$*$**$*', 4, 'F'),
('*$*$*$***$*$D$*', 6, 'D'),
('*$t**$*$***$***$**$*', 2, 't12'),
('*$**$*$***$**t$**$*', 5, '12t')
) i ([Signature Key],[Group Id],Portion)
CROSS APPLY master.dbo.DelimitedSplit8K([Signature Key],'$') k
WHERE k.ItemNumber = i.[Group Id];
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply