Trying to match non-quoted sections
-
Given the name of an SQL table, I am looking to add quotes as necessary (with an loose definition of "necessary"). With SQL Server, the parts of the name should be wrapped in brackets (
[and]), MySQL uses backticks (`). So, for example:database.schema.table
database.schema.[table]
database.[schema].table
[database].[schema].tableshould all be transformed to:
[database].[schema].[table]
What I have working; is capturing Wrapped and Unwrapped sections separately, wrapping the Unwrapped sections, and joining the sections back together. But it occurred to me that if I could Match only the Unwrapped sections, I could use Replace. However, I have so far been unsuccessful in my attempts (otherwise I wouldn't be posting). Does anyone out there have an idea of how to do this? I'm thinking it may involve Balancing Groups, but I've never used them before so I'm finding them confusing. This is not urgent. :-D Edit: I must have been over-thinking it :doh: . What I have now is
(?<=^|\.)[^\[\]\.]+(?=$|\.) -
Given the name of an SQL table, I am looking to add quotes as necessary (with an loose definition of "necessary"). With SQL Server, the parts of the name should be wrapped in brackets (
[and]), MySQL uses backticks (`). So, for example:database.schema.table
database.schema.[table]
database.[schema].table
[database].[schema].tableshould all be transformed to:
[database].[schema].[table]
What I have working; is capturing Wrapped and Unwrapped sections separately, wrapping the Unwrapped sections, and joining the sections back together. But it occurred to me that if I could Match only the Unwrapped sections, I could use Replace. However, I have so far been unsuccessful in my attempts (otherwise I wouldn't be posting). Does anyone out there have an idea of how to do this? I'm thinking it may involve Balancing Groups, but I've never used them before so I'm finding them confusing. This is not urgent. :-D Edit: I must have been over-thinking it :doh: . What I have now is
(?<=^|\.)[^\[\]\.]+(?=$|\.)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))); -
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