I firstly added one off static table with ALL possibilities of my wildcard results (this company has a 4 character nvarchar code as their localities and they wildcard their locals) i.e. they may have 456? which would give them 456[1] to 456[Z] i.e 0-9 & a-z
I had to write a script to pull the current user (declare them) and pull the masks for the declared user.
Create some temporary tables just basic ones to rank the row numbers for this current user
loop through each result (YOUR Or this Or that etc...)
Insert into the test Table.
Here is the script I used:
Drop Table #UserMasks
Drop Table #TESTUserMasks
Create Table #TESTUserMasks (
[User] [Int] NOT NULL,
[Mask] [Nvarchar](10) NOT NULL)
Create Table #UserMasks (
[RN] [Int] NOT NULL,
[Mask] [Nvarchar](10) NOT NULL)
DECLARE @User INT
SET @User = 74054
Insert Into #UserMasks
select ROW_NUMBER() OVER ( PARTITION BY ProntoUserID ORDER BY Id DESC) AS RN,
REPLACE(mask,'?','') Mask
from dbo.Access_Masks
where prontouserid = @User
DECLARE @TopFlag INT
SET @TopFlag = 1
WHILE (@TopFlag <=(select COUNT(*) from #UserMasks))
BEGIN
Insert Into #TestUserMasks
select (@User),Code from dbo.MaskArrayLookupTable
where code like (select Mask + '%' from #UserMasks Where RN = @TopFlag)
SET @TopFlag = @TopFlag + 1
END
GO
select * from #TESTUserMasks