C# Regular Expression

[hclugano]

Hello!
I need some help... I have a Text (an SQL-Create-Table-Statement) and
have to find the name of the table.
There are two ways, the tablename is written:
[dbo].[TABLENAME] (new SQL-standard) or dbo.TABLENAME (old)

Now I have to select the TABLENAME. I have no experience in Regular
Expressions. I tried it with a lot of Regex-Expressions and never get
the right thing.
For Example I tried: ([*)dbo(]*)\.([*)a-zA-Z0-9(]*)

Thanks for Help,
Greetings

 

[hclugano]

I found the Solution....
The searched RegEx is:
@"(\[*)(dbo)(\]*)\.(\[*)[a-zA-Z0-9]*(\]*)"

Greets

 

[Oliver Sturm]

I found the Solution....
The searched RegEx is:
@"(\[*)(dbo)(\]*)\.(\[*)[a-zA-Z0-9]*(\]*)"

Not too bad But a problem is that you are using the * quantifier
with the brackets, so this would also match more than one bracket. Plus,
you don't need all the parens. It should also work like this:

\[?dbo\]?\.\[?[a-zA-Z0-9]+\]?

Also note the + quantifier for the table name match. You wouldn't want
to find a table name of length 0, would you?


Oliver Sturm

 

[Guest]

Not too bad But a problem is that you are using the * quantifier

with the brackets, so this would also match more than one bracket. Plus,
you don't need all the parens. It should also work like this:

\[?dbo\]?\.\[?[a-zA-Z0-9]+\]?

Ok to use this regular exression but i would like to add few scenarios where
this will fail.

1. When using [] the table name can contain special charecters and even
spaces?
2. And what about _ and other allowed charecters in table name?
3. Table names are case insensitive in SQL so DBO, Dbo, dbo and other
similar cases are also valid.

So my regular expression would be:
\[?dbo\]?\.((\[[^\]]+\])|([^\s]+))

Also note I have added the group capture () so that we can extract the
tablename when required.

<CODE>
string str = "Select * from DBO.Table_Name Where a = 3";
string pattern = @"\[?dbo\]?\.((\[[^\]]+\])|([^\s]+))";
Regex reg = new Regex(pattern, RegexOptions.IgnoreCase);
Match m = reg.Match(str);
if(m.Success)
{
string match = m.Groups[1].Value;
Console.WriteLine("Match Found: " + match);
}
else
{
Console.WriteLine("Match Not Found");
}

</CODE>

Here we will get
[Table Name]
in case using brackets, i have retained the brackets as this is necessary
when special charecters like space is used in table names.

OR

Table_Name
in other cases

Hope it will help.

 

[Oliver Sturm]

Not too bad But a problem is that you are using the * quantifier
with the brackets, so this would also match more than one bracket. Plus,
you don't need all the parens. It should also work like this:

\[?dbo\]?\.\[?[a-zA-Z0-9]+\]?

Ok to use this regular exression but i would like to add few scenarios where
this will fail.

Sure, you are right. I wasn't targeting my changes at complete
compliance with the naming scheme.

1. When using [] the table name can contain special charecters and even
spaces?
2. And what about _ and other allowed charecters in table name?
3. Table names are case insensitive in SQL so DBO, Dbo, dbo and other
similar cases are also valid.

So my regular expression would be:
\[?dbo\]?\.((\[[^\]]+\])|([^\s]+))

I think the expression you have depends a bit on what you want to do.
For example, your expression is obviously a bit better, because it
targets a few more cases. But then it doesn't make sure that any of the
table name syntax variants actually contain only the allowed characters.
So if you just want to parse the string and you don't care (at this
point) if the result is really valid, your expression is probably fine.
But if you get the input from an unknown source and you want to make
sure that it's valid, you'll probably have to do more than that.

Just a heads up, really.



Oliver Sturm

 

[Greg Bacon]

: I need some help... I have a Text (an SQL-Create-Table-Statement) and
: have to find the name of the table.
: There are two ways, the tablename is written:
: [dbo].[TABLENAME] (new SQL-standard) or dbo.TABLENAME (old)
:
: Now I have to select the TABLENAME. I have no experience in Regular
: Expressions. I tried it with a lot of Regex-Expressions and never get
: the right thing.
: For Example I tried: ([*)dbo(]*)\.([*)a-zA-Z0-9(]*)

We know that the table name is bracketed by CREATE TABLE on the front
and on the back the left-paren that introduces the columns, and we
can use this to extract the table name:

static void Main(string[] args)
{
string[] inputs = new string[]
{
"create table [dbo].[TABLENAME] (...)",
"CREATE TABLE dbo.TABLENAME (...)",
"CREATE TABLE db.dbo.TABLENAME (...)",
"create table TABLENAME (...)",
};

Regex tablename = new Regex(
@"^\s*CREATE\s+TABLE\s+(" +
@".*? \[ (?<tablename>[^]]+) \] |" +
@".*? \. (?<tablename>\w+) |" +
@" (?<tablename>\w+)" +
@")\s+\(",
RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace);

foreach (string input in inputs)
{
Console.WriteLine("input = [" + input + "]:");

Match m = tablename.Match(input);
if (m.Success)
Console.WriteLine(" match (" + m.Groups["tablename"] + ")");
else
Console.WriteLine(" no match");
}
}

Hope this helps,
Greg