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
declare @string nvarchar(2000) = 't12';
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
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