How to handle multiple wildcards with SQL Server

Question

Hello,

I have some filters in the following form:

Object A

+/- Start    End
----------------------------------------------
+     000000080000 000000090000
-     000000800500
+     054*

Object B

+/- Start    End
----------------------------------------------
+     000000090000 000000100000
+     00??00900500
-     000000900500
+     055*

It means:

Numbers between 000000080000 and 000000090000 except 000000800500, and numbers starting with 054 are associated with object A.

Numbers between 000000090000 and 000000100000 except 000000900500, numbers matching 00??00900500 (except 000000900500 of course), and numbers starting with 055 are associated with object B.

Example of the table structure:

CREATE TABLE dbo.Filter
(
     IDFilter int IDENTITY PRIMARY KEY
)

CREATE TABLE dbo.FilterRow
(
     IDFilterRow int IDENTITY PRIMARY KEY
    ,IDFilter  int  FOREIGN KEY REFERENCES dbo.Filter(IDFilter) NOT NULL
    ,Operator  bit --0 = -, 1 = + NOT NULL
    ,StartNumber varchar(50) NOT NULL
    ,EndNumber  varchar(50) 
)

CREATE TABLE dbo.[Object]
(
     IDObject int IDENTITY PRIMARY KEY
    ,Name  varchar(10) NOT NULL
    ,IDFilter int FOREIGN KEY REFERENCES dbo.Filter(IDFilter) NOT NULL
)

I need a way to make sure no numbers can get associated with more than 1 object, in SQL (or CLR), and I really have no clue how to do such a thing (besides bruteforce).

I do have a CLR function Utils.fIsInFilter('?8*', '181235467895') that supports wildcards and would return 1, if it helps...

Answer 1

Can you use a CLR function in SQL 2005?

It's possible in raw SQL using LIKE JOINS (where ? becomes [0-9] and * becomes %), perhaps followed by CAST, but this is what CLR functions are for...