Can't update status to match characters in case of signature key value is stars?

  • 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.Data

    Expected 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.

  • May be something like this.

    (Search this site for DelimitedSplit8K id you do not already have it)

    SELECT	i.[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.

  • 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.

    SELECT	i.[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 Key	Group Id	Portion	(No column name)
    *$*$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

    • This reply was modified 2 months, 2 weeks ago by  Steve Collins. Reason: typo

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins wrote:

    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

    SELECT	i.[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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply