Need some help with pattern matching

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need some help with pattern matching

Author

Topic

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2005-02-18 : 10:48:38

Hey everyone,

Using LIKE, I need to match a pattern of one or more spaces. What I am doing is attempting to set up a Profile trace to catch table schema changes, but NOT procedure changes.

LIKE 'ALTER TABLE%'

doesn't work because the developer may put more than one space between the two keywords.

LIKE 'ALTER%TABLE%'

works, but it also grabs any ALTER PROC command that has the world 'table' in its batch, which I don't want.

Since I know there cannot be any other keywords between 'ALTER' and 'TABLE', but there can be multiple spaces, I want to someone match just that. Maybe I'm missing something real obvious, but I couldn't figure it out.

Thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-18 : 10:56:09

well an option would be to remove all spaces and use
like 'altertable%'

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 11:35:52

patindex('alter%table%',c) = 1 and patindex('alter%[^ ]%table%',c) = 0

rockmoose

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2005-02-18 : 11:58:01

That's awesome, rockmoose. I'm sure I'll use that at some point. However for this current problem, I am attempting to do this in a Profiler trace filter, which only accepts an expression to be evaluated by LIKE. I don't think this will work there, unless I'm mistaken. Thanks, though.

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 12:32:04

You can have a like and a not like in the trace filter:
like('alter %table%')
not like('alter%[^ ]%table%')

maybe that will that work for you.

rockmoose

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2005-02-18 : 13:23:35

Thanks, rock, but that's a no-go also. The Profiler does an OR of all filter conditions, not an AND, therefore it doesn't restrict the results at all. Oh well.

Thanks anyway.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-18 : 13:53:54

[code]
like '%alter table%'
or like '%alter table%'
or like '%alter table%'
or like '%alter table%'
or like '%alter table%'
[/code]

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 17:23:21

Maybe you could add the following
EventClass: "Audit Object Derived Permission Event" to your trace, and ObjectType column.
Empirical results on my pc tell me that, ObjectType = 17 is table, and 8 is proc
It is eventnumber 118.

rockmoose

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2005-02-18 : 18:08:44

Rockmoose, you rock! That works perfectly. I could just kiss you! Well, maybe not, but you sure made my weekend. Thanks.

3P

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-19 : 03:49:52

A beer would be fine

At first I tried to replace all the and's to or's in the expression (DeMorgan's Law of Logic),
A and B = not(not A or not B)
But it just wouldn't rock, because You can't have a NOT expression in the trace filter.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources