Comparing wildcard strings - both sides of the comparison wildcarded

The problem: I have events arriving with a compound key. To simplify, let's say the key is:

OS+hostname+somedetail

Some examples might be:

• WIN+SERVER1+DB+PAY
• WIN+SERVER2+CPU_HIGH

We have various teams who can subscribe to these with a SQL wildcarded string. For example, a windows admin might subscribe to WIN+% and a DBA might subscribe to WIN+%+DB+%.

We've made a rod for our own backs by not nailing this down at the start but it's been a classic case of making it work first then make it work better when we can. For performance reasons I want to minimize the number of overlapping subscriptions. The data is both in excel and a MS SQL table. The problem is that although SQL server will agree WIN+% is LIKE WIN+SERVER1+DB+PAY it doesn't seem to agree that WIN+% is LIKE WIN+%+DB+% in a SQL SELECT. And most of the solutions I can google allow wildcards on only one side.

I think I can some of it by breaking one side of the wildcard comparison (which can be more complex) at each wildcard.

For example WIN+%+DB+% becomes SELECT field WHERE field LIKE 'WIN+%" and field LIKE '%+DB+%'

This seems to break down quite quickly, though possibly I could produce a list of likely cases.开发者_如何学运维

I'm working through a book on regexes and suspect they might be of use once I get a better grip. I have a fair toolkit on the server with access to more so am open to any solutions on most platforms.

In case anyone is interested - each match produces a ticket so 2 matches will produce 2 tickets and they may not cross refer causing extra work. This is across several data centers and we have several thousand subscriptions in place - mainly auto-generated by a "part-of-the-problem" "solution". On the plus side it keeps me in work.

I have 2 suggestions:

a) Think about breaking the big subscription strings into their parts. So you end with 3 columns: OS, HOSTNAME and DETAIL. Then you can query ... FROM SUBSCRIPTIONS WHERE event LIKE OS||'%' AND event LIKE '%'||HOST||'%' AND event LIKE '%'||DETAIL.

b) You have 8 cases: no wildcard, only OS as wildcard, only HOST as wildcard, only DETAIL as wildcard, OS and HOST as wildcard, OS and DETAIL as wildcard, HOST and DETAIL as wildcard, all 3 as wildcard. For each of this you can write a seperate query handling it. This would function as you described. This would be a bad solution if you have much more cases cause it is not easy to extend.