Trying to match non-quoted sections
-
Are you looking for this?
string sql = @"database.schema.table
database.schema.[table]
database.[schema].table
[database].[schema].tableshould all be transformed to:
[database].[schema].[table]
";
string pattern = @"(?:\[?(\w+)\]?)?\.\[?(\w+)\]?";
Func replace = m =>
(m.Groups[1].Success ? "[" + m.Groups[1].Value + "]" : "") + ".[" + m.Groups[2].Value + "]";
Console.WriteLine("{0}", Regex.Replace(sql, pattern, m=>replace(m)));Does that work for names that contain SPACEs?
my database.my schema.my tableAnd Excel worksheet names that include a dollar sign ($) at the end? (I realize those were not listed in the original spec.) -
Are you looking for this?
string sql = @"database.schema.table
database.schema.[table]
database.[schema].table
[database].[schema].tableshould all be transformed to:
[database].[schema].[table]
";
string pattern = @"(?:\[?(\w+)\]?)?\.\[?(\w+)\]?";
Func replace = m =>
(m.Groups[1].Success ? "[" + m.Groups[1].Value + "]" : "") + ".[" + m.Groups[2].Value + "]";
Console.WriteLine("{0}", Regex.Replace(sql, pattern, m=>replace(m)));Wait till Smitha tackles that post! :laugh:
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Wait till Smitha tackles that post! :laugh:
Luc Pattyn [My Articles] Nil Volentibus Arduum
Hi Luc, Aaaah! You read my whine... ;-) I was quite upset - but I cooled down again :-) Cheers Andi [Edit] PS: ...and the tip is reverted to the "original" state again... [/Edit]
-
Does that work for names that contain SPACEs?
my database.my schema.my tableAnd Excel worksheet names that include a dollar sign ($) at the end? (I realize those were not listed in the original spec.)You will run into problems here. The problem arises with spaces in the name, since the following pattern:
...([\w\s]+)...matchesdelete my databaseas well asdelete databaseIn this case I guess you don't get away without a parser (use the Regex for tokenizing, use the parser to detect all commands and translate the arguments where needed). Any names without spaces get easily translated, though, e.g.:string pattern = @"(?:\[?([\w\$]+)\]?)?\.\[?([\w\$]+)\]?";
And if you have optional spaces around "[" and ".", the following a bit more complicted regex will do:
...
string open = @"(?:\[\s*?)?";
string close = @"(?:\s*?\])?";
string ident = @"([\w\$]+)";
string prefix = @"(?:" + open + ident + close + @"\s*?)?";
string suffix = @"(?:" + open + ident + close + @")";
string pattern = prefix + @"\.\s*?" + suffix;
... -
You will run into problems here. The problem arises with spaces in the name, since the following pattern:
...([\w\s]+)...matchesdelete my databaseas well asdelete databaseIn this case I guess you don't get away without a parser (use the Regex for tokenizing, use the parser to detect all commands and translate the arguments where needed). Any names without spaces get easily translated, though, e.g.:string pattern = @"(?:\[?([\w\$]+)\]?)?\.\[?([\w\$]+)\]?";
And if you have optional spaces around "[" and ".", the following a bit more complicted regex will do:
...
string open = @"(?:\[\s*?)?";
string close = @"(?:\s*?\])?";
string ident = @"([\w\$]+)";
string prefix = @"(?:" + open + ident + close + @"\s*?)?";
string suffix = @"(?:" + open + ident + close + @")";
string pattern = prefix + @"\.\s*?" + suffix;
...Andreas Gieriet wrote:
matches
delete my database
as well as
delete databaseI expect the string to contain only the database, schema, and table names.
-
Andreas Gieriet wrote:
matches
delete my database
as well as
delete databaseI expect the string to contain only the database, schema, and table names.
The Regex sees a line like
aaa bbb ccc . ddd . eee fffWhat part ofaaa bbb cccis the database name? Onlycccorbbb ccc, etc.? You see the problem? The same foreee fff. Non-escaped/non-wrapped spaces in names is guess work to make them wrapped into[...]. I.e. to get fromaaa bbb ccc ....toaaa [bbb ccc] ....is rather difficult, unless you know whataaameans or you say from outside thatbbb cccis a single name. Quite a challenge. ;-) Cheers Andi -
The Regex sees a line like
aaa bbb ccc . ddd . eee fffWhat part ofaaa bbb cccis the database name? Onlycccorbbb ccc, etc.? You see the problem? The same foreee fff. Non-escaped/non-wrapped spaces in names is guess work to make them wrapped into[...]. I.e. to get fromaaa bbb ccc ....toaaa [bbb ccc] ....is rather difficult, unless you know whataaameans or you say from outside thatbbb cccis a single name. Quite a challenge. ;-) Cheers AndiThat should result in
[aaa bbb ccc ].[ ddd ].[ eee fff] -
That should result in
[aaa bbb ccc ].[ ddd ].[ eee fff]The line
aaa bbb ccc.ddd eee ...could beALTER TABLE dbo.tVersion ADD ...which in your approach would result in[ALTER TABLE dbo].[tVersion ADD] ...Forget about spaces or get as input the individual names (db name, table name, etc.) or make a parser that detects all language constructs and their db, table, etc. positions... I still think it's not worth the effort with names that contain spaces - too fragile. Cheers Andi -
The line
aaa bbb ccc.ddd eee ...could beALTER TABLE dbo.tVersion ADD ...which in your approach would result in[ALTER TABLE dbo].[tVersion ADD] ...Forget about spaces or get as input the individual names (db name, table name, etc.) or make a parser that detects all language constructs and their db, table, etc. positions... I still think it's not worth the effort with names that contain spaces - too fragile. Cheers AndiNo, the string contains only the database, schema, and table name separated by periods as per the original post.
-
No, the string contains only the database, schema, and table name separated by periods as per the original post.
I was confused since I understood (say: assumed...) that you have an SQL script that you want to patch... Never assume anything ;-) In that case your initial regex is probably the simplest solution. Cheers Andi