如何使用SQL Server处理多个通配符

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.

除000000800500之外的000000080000和000000090000之间的数字以及从054开始的数字与对象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.

000000090000和000000100000之间的数字除了000000900500,数字匹配00 ?? 00900500(当然除了000000900500),以055开头的数字与对象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).

我需要一种方法来确保在SQL(或CLR)中没有数字可以与多个对象相关联,而且我真的不知道如何做这样的事情(除了暴力之外)。

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

我有一个支持通配符的CLR函数Utils.fIsInFilter('?8 *','181235467895'),如果有帮助,将返回1 ...

1 个解决方案

#1

Can you use a CLR function in SQL 2005?

你能在SQL 2005中使用CLR功能吗?

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...

在原始SQL中可以使用LIKE JOINS(其中?变为[0-9]且*变为%),也许后面是CAST,但这就是CLR函数的用途......

#1