CODEWITHSUNDEEP
c#.netsqlregexvb.net
Stefan Steiger
Stefan Steiger

Reputation: 82246

Regex pattern that matches SQL object (table/function/view)?

Question:

Given a SQL string like

CREATE VIEW TestView AS
SELECT value1, value2 
FROM TABLE_0
UNION
(SELECT * FROM TABLE_2) AS value1
,value2 
FROM TABLE_12 
UNION
SELECT * FROM TABLE_3

(in lowercase)

And an array of string like 

string[] tables = new string[]{"table_1", "table_2", "table_3"}

Now I did:

if (strViewDefinition.Contains(strObjectName)) // aaaaargh

for each strObjectName in tables, to check whether or not the view depends on this object.

But this fails on table_12 (and results in cyclic dependencies) for example, because tables contains "table_1". Aaargh.

I need a regex that can check if the view definition contains a function name, table-valued function or another view...

( For checking with System.Text.RegularExpressions.Regex.IsMatch( )

My trial was:

string whateverneedsescaping= System.Text.RegularExpressions.Regex.Escape(@"+-*\/%=,\n\r");
string fieldsep = @"[\s\n\r," + whateverneedsescaping+ "]*";
string strPattern = fieldsep + "VIEW/FUNCTION_NAME" + fieldsep;

But it fails, plus it doesn't account for object names embraced in brackets, like

 "[TABLE_NAME]"

Anybody can help ?

Upvotes: 4

Views: 750

Answers (3)

Stefan Steiger
Stefan Steiger

Reputation: 82246

This does the job:

string strSchema = "dbo";
string strRegexObjectName = System.Text.RegularExpressions.Regex.Escape(strObjectName);
string strPattern = @"[\s\+\-\(,/%=\*\\](\[?" + strSchema + @"\]?\.)?\[?" + strRegexObjectName + @"\]?([\s\+\-\),/%=\*\\]|$)";

Upvotes: 0

Robbie
Robbie

Reputation: 19500

I think the reason it doesn't work is because you have the - character in your set. This character is not escaped by Regex.Escape and so your character set ends up containing the following:

[\s\n\r,\+-\*\\/%=,\\n\\r]

The - in this set tries to create a range between the literal characters + and * which doesn't make any sense and hence it throws an ArgumentException with the message [x-y] range in reverse order.

While i'm not 100% sure i understand your exact requirements, i think that the suggestion about using word boundaries from Qtax would work.

All i would add is that you could account for the table names surrounded with [] by changing it to:

\[?\btable_1\b]?

Upvotes: 2

Qtax
Qtax

Reputation: 33918

Would the expression \bname\b suffice? \b is a word boundary.

For example \btable_1\b would not match table_12.

If you have many names you could check for them all together with an expression like \b(?:foo|bar|baz)\b.

Upvotes: 1

Related Questions