Regex expression for parsing SQL Server schema and tablename

Question

I am trying to parse schema and table name using a regular expression. I am using C# (.NET Core) and System.Text.RegularExpressions.

Here are examples how the input can look like:

Schema.TableName
Schema . TableName
[Schema].[TableName]
[Schema] . [TableName]
Schema. [TableName]
[SchemaPart1.Part2.Part  3].[Table . Name]

And of course other variations that SQL Server accepts when you want to create a table using table and schema name.

Also, I am supporting other databases (MySql & Postgres), so I will reuse the same regex, but replace the [] with the right quotation character (` or " ). But I used the Sql Server examples because the start and ending quotation character are different.

My current regular expression looks like this:

\.?\[.+?\]|[^\[]+?(?=\.)|[^\[]+

This does work most of the times. But e.g. in this case [Schema] . [TableName] I get a match for whitespaces in between which I am not quite sure how to filter out.

Any help would be appreciated.

Regular expression might not be the best toll here. Maybe this is helpful. Especially the ParseSchemaObjectName() method seems to do what you want. — sticky bit
– sticky bit, Commented Mar 18, 2020 at 21:37
If you absolutely want to do this with an expression, I would suggest matching the parts you expect in sequence: schema, period with optional space, table; that also makes it possible to capture, if necessary. For example: ^(?:\[[^]]+]|\w+) *\. *(?:\[[^]]+]|\w+). — oriberu
– oriberu, Commented Mar 18, 2020 at 21:53
@stickybit : Thanks for replay. This is actually a good hint. But I am planning to reuse the same regex to parse the table names for other databases, e.g. "public"."table1" for Postgres DBs. TSqlTParser would only work for Sql Server. — Andreas
– Andreas, Commented Mar 20, 2020 at 19:26
@oriberu I tried to get your expression work with regex tester (regexstorm.net/tester), but the expression didn't work there as expected. What did I wrong? — Andreas
– Andreas, Commented Mar 20, 2020 at 19:27
@Andreas Be sure to check Multiline to the right before running the regex. regex101.com doesn't support .NET-style syntax per se, but it's so close to PCRE that it might be worth using that site for most of the cases; I find it easier to use for tweaking, since you see live results: regex101.com/r/h793L7/1. — oriberu
– oriberu, Commented Mar 20, 2020 at 20:00

oriberu · Accepted Answer · 2020-03-24 11:10:06Z

One possible way is to match schema, table and delimiters at once and use capturing groups to get the names separated from surrounding fluff. For example:

`/^(\[[^]]+]|\w+) *\. *(\[[^]]+]|\w+)/gm`

See regex demo.

C# example:

using System;
using System.Text.RegularExpressions;
// ...
String text = "Schema.TableName\nSchema . TableName\n[Schema].[TableName]\n[Schema] . [TableName]\nSchema. [TableName]\n[SchemaPart1.Part2.Part  3].[Table . Name]";
Regex re = new Regex(@"^(\[[^]]+\]|\w+) *\. *(\[[^]]+\]|\w+)", RegexOptions.Multiline);
Match match = re.Match(text);

while (match.Success) {
    Console.WriteLine("( '{0}', '{1}' )", match.Groups[1].Value, match.Groups[2].Value);
    match = match.NextMatch();
}

Output:

'Schema', 'TableName'
'Schema', 'TableName'
'[Schema]', '[TableName]'
'[Schema]', '[TableName]'
'Schema', '[TableName]'
'[SchemaPart1.Part2.Part  3]', '[Table . Name]'

OR

If you want to match schema or table without differentiating between them, you could try a variation of your expression. Be advised that this would also match simple strings like foo. For example:

`/\.? *(?:\[[^]]+\]|\w+)/g`

See regex demo.

C# example (using and text same as above):

MatchCollection matches = Regex.Matches(text, @"\.? *(?:\[[^]]+\]|\w+)");
foreach (Match m in matches) Console.WriteLine("'{0}'", m);

Output:

'Schema'
'.TableName'
'Schema'
'. TableName'
'[Schema]'
'.[TableName]'
'[Schema]'
'. [TableName]'
'Schema'
'. [TableName]'
'[SchemaPart1.Part2.Part  3]'
'.[Table . Name]'

Luc Vandenbroucke · Accepted Answer · 2022-05-06 09:50:51Z

0

or with labelling the parts , and removing the brackets

(?:(?:\[(?<server>[^\]]*)\]|(?<server>[^\.]*))\.)?(?:(?:\[(?<catalog>[^\]]*)\]|(?<catalog>[^\.]*))\.)?(?:(?:\[(?<schema>[^\]]*)\]|(?<schema>[^\.]*))\.)?(?:\[(?<object>[^\]]*)\]|(?<object>[^\.]*))$

!this needs righttoleft parsing

public static Regex regex = new Regex(
"(?:(?:\\[(?<server>[^\\]]*)\\]|(?<server>[^\\.]*))\\.)?(?:(?" +
":\\[(?<catalog>[^\\]]*)\\]|(?<catalog>[^\\.]*))\\.)?(?:(?:\\[" +
"(?<schema>[^\\]]*)\\]|(?<schema>[^\\.]*))\\.)?(?:\\[(?<objec" +
"t>[^\\]]*)\\]|(?<object>[^\\.]*))$",
RegexOptions.IgnoreCase | RegexOptions.Singleline 
| RegexOptions.CultureInvariant | RegexOptions.Compiled
| RegexOptions.RightToLeft );

answered May 6, 2022 at 9:50

Luc Vandenbroucke

1391 silver badge6 bronze badges

Collectives™ on Stack Overflow

Regex expression for parsing SQL Server schema and tablename

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related