can't update status to match Characters where signature key value is

ahmed elbarbary

6y962

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 r
cross apply
dbo.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.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah6y ago
test it with
insert into #Ref(SignatureKey,GroupId,PortionKey,status)
values
('A$*$***$*$**$**$*',1,'A',NUll),
('*$*$***$*$**$**$Z',7,'Z',NUll),
('A**$*$***$*$**$**$*',1,'A12',NUll),
('*$*$***$*$**$**$**Z',7,'12Z',NUll),
('**P$*$***$*$**$**$*',1,'12P',NUll),
('*$*$***$*$**$**$D**',7,'D12',NUll),
('*$*$***$C$**$**$*',4,'C',NUll),
('*$*$C$***$**$**$*',3,'s',NUll),
('*$*$*$FG$*$**$*',4,'F',NUll),
('*$*$*$***$*$D$*',6,'D',NUll),
('*$t**$*$***$***$**$*',2,'t12',NUll),
('*$**$*$***$**t$**$*',5,'12t',NUll)
+1
Shahriar Nikkhah6y ago
this should be final i hope
SELECT  *
    --,  LEN(SignatureKey) AS LenSignatureKey
    ,   StatusNew = CASE    --WHEN [Position] = 0 THEN 'Not Match Characters'
                            --WHEN [CharacterAfter] <> '$' THEN 'Not Match Characters'
                            --WHEN [CharacterBefore] <> '$' THEN 'Not Match Characters'
                            WHEN [Position] > 1 AND [CharacterAfter] = '$' AND [CharacterBefore] = '$' THEN '       Match Characters'
                            WHEN [Position] = 1 AND [CharacterAfter] = '$' THEN '       Match Characters'
                            --WHEN [Position] = LEN(SignatureKey) AND [CharacterBefore] = '$' THEN '       Match Characters'
                            WHEN LEN(SignatureKey)  = [Position] + LEN(PortionKeyNew) - 1 AND [CharacterBefore] = '$' THEN '       Match Characters'
                            ELSE 'Not Match Characters'
                    END
FROM    (   SELECT  *
                ,   Position = CHARINDEX (PortionKeyNew , SignatureKey)
                ,   CharacterBefore = SUBSTRING(SignatureKey ,CHARINDEX (PortionKeyNew , SignatureKey) - 1 ,1)
                ,   CharacterAfter = SUBSTRING(SignatureKey ,CHARINDEX (PortionKeyNew , SignatureKey) + LEN(PortionKeyNew) ,1 )

            FROM    (   SELECT  *
                            ,   PortionKeyNew = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PortionKey , '0' , '*') , '1' , '*') , '2' , '*') , '3' , '*') , '4' , '*') , '5' , '*') , '6' , '*') , '7' , '*') , '8' , '*') , '9' , '*')
                        FROM    #Ref
                    ) AS itbl
        ) AS MT
+1
Shahriar Nikkhah6y ago
You may need to add more scenarios to the CASE statement
WHEN [Position] > 1 AND [CharecterAfter] = '$' AND [CharecterBefore] = '$' THEN '       Match Characters'
WHEN [Position] = 1 AND [CharecterAfter] = '$' THEN '       Match Characters'
WHEN [Position] = LEN(SignatureKey) AND [CharecterBefore] = '$' THEN '       Match Characters'
WHEN LEN(SignatureKey)  = [Position] + LEN(PortionKeyNew) - 1 AND [CharecterBefore] = '$' THEN '       Match Characters'
ELSE 'Not Match Characters'
+1
Shahriar Nikkhah6y ago
Just changed the CASE statement to cover scenarios like

('A$*$***$*$**$**$*',1,'A',NUll),
('*$*$***$*$**$**$Z',7,'Z',NUll),
SELECT  *
    ,   StatusNew = CASE    --WHEN [Position] = 0 THEN 'Not Match Characters'
                            --WHEN [CharecterAfter] <> '$' THEN 'Not Match Characters'
                            --WHEN [CharecterBefore] <> '$' THEN 'Not Match Characters'
                            WHEN [Position] > 1 AND [CharecterAfter] = '$' AND [CharecterBefore] = '$' THEN '       Match Characters'
                            WHEN [Position] = 1 AND [CharecterAfter] = '$' THEN '       Match Characters'
                            WHEN [Position] = LEN(SignatureKey) AND [CharecterBefore] = '$' THEN '       Match Characters'
                            ELSE 'Not Match Characters'
                    END
FROM    (   SELECT  *
                ,   Position = CHARINDEX (PortionKeyNew , SignatureKey)
                ,   CharecterBefore = SUBSTRING(SignatureKey ,CHARINDEX (PortionKeyNew , SignatureKey) - 1 ,1)
                ,   CharecterAfter = SUBSTRING(SignatureKey ,CHARINDEX (PortionKeyNew , SignatureKey) + LEN(PortionKeyNew) ,1 )

            FROM    (   SELECT  *
                            ,   PortionKeyNew = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PortionKey , '0' , '*') , '1' , '*') , '2' , '*') , '3' , '*') , '4' , '*') , '5' , '*') , '6' , '*') , '7' , '*') , '8' , '*') , '9' , '*')
                        FROM    #Ref
                    ) AS itbl
        ) AS MT
+1
Shahriar Nikkhah6y ago
1. SELECT *
2. , StatusNew = CASE --WHEN [Position] = 0 THEN 'Not Match Characters'
3. --WHEN [CharecterAfter] <> '$' THEN 'Not Match Characters'
4. --WHEN [CharecterBefore] <> '$' THEN 'Not Match Characters'
5. WHEN [Position] > 0 AND [CharecterAfter] = '$' AND [CharecterBefore] = '$' THEN ' Match Characters'
6. ELSE 'Not Match Characters'
7. END
8. FROM ( SELECT *
9. , Position = CHARINDEX (PortionKeyNew , SignatureKey)
10. , CharecterBefore = SUBSTRING(SignatureKey ,CHARINDEX (PortionKeyNew , SignatureKey) - 1 ,1)
11. , CharecterAfter = SUBSTRING(SignatureKey ,CHARINDEX (PortionKeyNew , SignatureKey) + LEN(PortionKeyNew) ,1 )
13. FROM ( SELECT *
14. , PortionKeyNew = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PortionKey , '0' , '*') , '1' , '*') , '2' , '*') , '3' , '*') , '4' , '*') , '5' , '*') , '6' , '*') , '7' , '*') , '8' , '*') , '9' , '*')
15. FROM #Ref
16. ) AS itbl
17. ) AS MT
+1

Shahriar Nikkhah6y ago

and is this consider MATCHING CHARECTERS ??? i assume so

('*$*$***$C$**$**$*',4,'C',NUll),

Shahriar Nikkhah6y ago

Why is the mentioned not matching????? doesnt D = D ?????

*$*$*$***$*$D$* 6 D Not Match Characters