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

Question

0.00/5 (No votes)

See more:

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

SQL

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.

SQL

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)

Expected Result :

SQL

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.

What I have tried:

SQL

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

Posted 7-Sep-20 17:28pm

ahmed_sa

Updated 8-Sep-20 6:01am

Add a Solution

Comments

Maciej Los 8-Sep-20 2:22am

What is the body of dbo.Split function?

ahmed_sa 8-Sep-20 2:58am

dbo.split are built in function

Richard Deeming 8-Sep-20 5:21am

No it's not. The built-in function is STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^], which was added in SQL Server 2016.

Maciej Los 8-Sep-20 14:44pm

:thumbsup:

ahmed_sa 8-Sep-20 6:30am

sorry function exist on
dbo.split function exist on
https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2020-09-08T06:02:00

I've timed out on this so I will post the technique I'm applying and what I have so far. Hopefully I'll be able to get back to you.

For any of your PortionKey I was generating (via a table valued function) a list of the possible matches.

For example if I have the string t12 I want 't12','t**','*1*','**2','t1*','t*2','*12'. Then you can just match those against the Signature key

To generate those combinations I was using this

SQL

declare @string nvarchar(2000) = 't12';
--DECLARE @wildchar char(1) = '*';

IF OBJECT_ID('tempdb..#Combos') IS NOT NULL DROP TABLE #Combos
IF OBJECT_ID('tempdb..#Patterns') IS NOT NULL DROP TABLE #Patterns

;WITH numsCTE AS
(
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 1
    FROM    numsCTE
    WHERE num < LEN(@string) 
)
,RecurCTE AS 
(
	SELECT num, CAST(num AS NVARCHAR(2000)) as Combination, REPLICATE('*', LEN(@string)) as Pattern
	FROM numsCTE
	UNION ALL
	SELECT n.num,CAST(r.Combination + ',' + CAST(n.num AS NVARCHAR(10)) AS NVARCHAR(2000)), REPLICATE('*', LEN(@string)) as Pattern
	FROM RecurCTE r
INNER JOIN numsCTE n ON n.num > r.num
)
SELECT ROW_NUMBER() OVER (ORDER BY LEN(Combination),Combination) AS RN, Combination, Pattern
INTO #combos
FROM RecurCTE
ORDER BY LEN(Combination),Combination;

select o.RN, [value] 
INTO #Patterns
from #combos o 
cross apply (select o.RN, [value] FROM STRING_SPLIT(o.combination,',') ) AS CA

select * from #combos
select * from #Patterns

The first set of CTEs generates all the combinations of positions in the string (Warning - so it will only work up to 9 characters in the string!!). The end bit gets a list of all the substitutions I need to make - I got as far as

SQL

UPDATE o
SET o.Pattern = STUFF(o.Pattern, CAST(b.value AS int), 1, SUBSTRING(@string, CAST(b.value AS int),1))
FROM #combos o 
inner join #Patterns b on o.RN =b.rn

but that is not working and I don't have time just now to work out why. Hopefully you get the gist of what I'm suggesting