locked
Regex replace samples

    Question

  • I need to go through bunch of sql query files and do some replacement for column name changes and table name changes schemaname

    changes etc.

    I need to change .[ColumnName] to .[NewColumnName]

    or

    .ColumnName + (whitespace or "[") to  .NewColumnName + (whitespace or "[")

    I need to do similar to TableName and SchemaName and Database name etc. This is to do some global change to database object name changes. How can I accomplish the above conditions with a simple regex replace?

    Thank you


    Gokhan Varol
    Friday, January 27, 2012 7:06 PM

Answers

  • using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT a.OldName FROM ..");
                sb.AppendLine("SELECT a.[OldName] FROM .. ");
                sb.AppendLine("SELECT a.OldName[id] FROM ..");
                sb.AppendLine("SELECT a.OldName\"id\" FROM ..");
                sb.AppendLine("SELECT a.OldName'id' FROM .. ");
                sb.AppendLine("SELECT TABLE.[TESTVAL] FROM TABLE");
                String revised = Regex.Replace(sb.ToString(), @"\b(a|TABLE)\.\[?(OldName|TESTVAL)(\]|\[|""|')?(id)?(\]|""|')?",
                    MyReplace);
                Console.WriteLine("Old SQL");
                Console.WriteLine("---------------------");
                Console.WriteLine(sb);
                Console.WriteLine("---------------------");
                Console.WriteLine("");
    
                Console.WriteLine("New SQL");
                Console.WriteLine("---------------------");
                Console.WriteLine(revised);
                Console.WriteLine("---------------------");
                Console.WriteLine("");
                Console.Read();
            }
    
            static String MyReplace(Match m)
            {       
                return m.Value.Replace("OldName", "NewName").Replace("TESTVAL", "NewName");
            }
        }
    }
    
    


    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    • Marked as answer by Paul Zhou Tuesday, February 07, 2012 4:58 AM
    Friday, January 27, 2012 9:48 PM
  • Perhaps you are having difficulty understanding. The MatchEvaluator is a delegate that points to a method. In other words it is saying that you will handle the replacement in your own method that follows the MatchEvaluator signature. Which is a Match class in its parameter having the method return a string. What this allows you to do is go into greater specificity and hone the replacement in finer detail.

    So (String, String, YOUR own method to handle the replacement)

    So the parameter is asking which method will handle the replacement. In the example I gave, the MyReplace method will take care of that.


    John Grove, Senior Software Engineer http://www.digitizedschematic.com/

    • Edited by JohnGrove Monday, January 30, 2012 2:32 PM
    • Marked as answer by Paul Zhou Tuesday, February 07, 2012 4:57 AM
    Monday, January 30, 2012 2:31 PM

All replies

  • I added some more samples, the only whitespace used here is a single space but it can also be a tab or newline (before or after the columnname). id text below was used as a column alias to explain a [ or ' or " can be right after a columnname instead of a white space.
    What regex can be used to replace the oldname to newname in below samples.
    ColumnName pattern
    SELECT a.OldName FROM .. to SELECT a.NewName FROM ..
    SELECT a.[OldName] FROM .. to SELECT a.[NewName] FROM
    SELECT a.OldName[id] FROM .. to SELECT a.NewName[id] FROM .. ..
    SELECT a.OldName"id" FROM .. to SELECT a.NewName"id" FROM .. ..
    SELECT a.OldName'id' FROM .. to SELECT a.NewName'id' FROM .. ..
    Thank you

    Gokhan Varol
    Friday, January 27, 2012 7:56 PM
  • Can't you simple say find \.OldName and replace with \.NewName
    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Friday, January 27, 2012 8:41 PM
  • Personally, I would copy/paste to MS Word and do a simple find/replace Ctrl+H

    You only have what 5 differences. You're going to throw away the script anyway right?

    It would take longer to test the regex than to just do it manually.


    Try to look ahead so you're not forced to look behind.
    Friday, January 27, 2012 8:44 PM
  • There is couple thousand sql files and there are over 1500 table name or columnname or schema name or database name changes I need to replace the scripts. I wrote a c# code that does a simple replace which does the initial job but realized there are more things I need to consider like white space etc (if I replace "Name" to "NewName" that also replaces "OwnerName" to "OwnerNewName" etc). We are doing a database migration that is why we do need this.
    Gokhan Varol
    Friday, January 27, 2012 8:58 PM
  • Do you have PowerGrep?
    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Friday, January 27, 2012 9:04 PM
  • no I don't. 

    What we are trying to do is we have a table with before and after values for columnnames, or table names, or schemanames or object names etc , which has about 1500 + before and after values. I have a c# application that loops through files in a directory recursive and for each file loops through the table (which is in memory) which has before and after values and replaces the values, it needs to also handle new lines brackets single double quotes during replace. Not sure a software like powergrep can get a list of 1500+ items and replace them recursively in files.


    Gokhan Varol
    Friday, January 27, 2012 9:08 PM
  • Find:

    \ba.\[?OldName(?:\]|\[|"|')?(?:id)?(?:\]|"|')?

    Check to see if this captures all of your SELECT statements that you want captured. If this works we can devise a solution.


    John Grove, Senior Software Engineer http://www.digitizedschematic.com/

    • Edited by JohnGrove Friday, January 27, 2012 9:26 PM
    Friday, January 27, 2012 9:25 PM
  • did not capture this. 

    SELECT TABLE.[TESTVAL] FROM TABLE

     

    Let me ask you a question, if there are let say 30 possibilites because of before and after combinations, if I use C# and string.Replace method repeat the replace 30 times would that be worse in performance than using regex, I am not familiar with regex I can add 30 lines of c# code quicker maybe?


    Gokhan Varol
    Friday, January 27, 2012 9:35 PM
  • I'm just going by the data you have presented, but only YOU know your data:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT a.OldName FROM ..");
                sb.AppendLine("SELECT a.[OldName] FROM .. ");
                sb.AppendLine("SELECT a.OldName[id] FROM ..");
                sb.AppendLine("SELECT a.OldName\"id\" FROM ..");
                sb.AppendLine("SELECT a.OldName'id' FROM .. ");
                String revised = Regex.Replace(sb.ToString(), @"\ba.\[?OldName(\]|\[|""|')?(id)?(\]|""|')?",
                    MyReplace);
                Console.WriteLine("Old SQL");
                Console.WriteLine("---------------------");
                Console.WriteLine(sb);
                Console.WriteLine("---------------------");
                Console.WriteLine("");
    
                Console.WriteLine("New SQL");
                Console.WriteLine("---------------------");
                Console.WriteLine(revised);
                Console.WriteLine("---------------------");
                Console.WriteLine("");
                Console.Read();
            }
    
            static String MyReplace(Match m)
            {       
                return m.Value.Replace("OldName", "NewName");
            }
        }
    }
    
    

    If you have like 30 or so possibilities maybe another solution is needed.


    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Friday, January 27, 2012 9:42 PM
  • using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT a.OldName FROM ..");
                sb.AppendLine("SELECT a.[OldName] FROM .. ");
                sb.AppendLine("SELECT a.OldName[id] FROM ..");
                sb.AppendLine("SELECT a.OldName\"id\" FROM ..");
                sb.AppendLine("SELECT a.OldName'id' FROM .. ");
                sb.AppendLine("SELECT TABLE.[TESTVAL] FROM TABLE");
                String revised = Regex.Replace(sb.ToString(), @"\b(a|TABLE)\.\[?(OldName|TESTVAL)(\]|\[|""|')?(id)?(\]|""|')?",
                    MyReplace);
                Console.WriteLine("Old SQL");
                Console.WriteLine("---------------------");
                Console.WriteLine(sb);
                Console.WriteLine("---------------------");
                Console.WriteLine("");
    
                Console.WriteLine("New SQL");
                Console.WriteLine("---------------------");
                Console.WriteLine(revised);
                Console.WriteLine("---------------------");
                Console.WriteLine("");
                Console.Read();
            }
    
            static String MyReplace(Match m)
            {       
                return m.Value.Replace("OldName", "NewName").Replace("TESTVAL", "NewName");
            }
        }
    }
    
    


    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    • Marked as answer by Paul Zhou Tuesday, February 07, 2012 4:58 AM
    Friday, January 27, 2012 9:48 PM
  • I think it's very close, I am not able to follow this code though, what is the MyReplace function and how to I pass in before and after values into it and how do I make it case insensitive.

    I am replacing Transact SQL Query files, they can be adhoc sql or they can be stored procedure , function etc not sure .

    The below query is valid, it returns the same column "name" out from sys.objects table, therefore the columnname can have a tab, space, new line, comma or dot in front and it can have a comma, space, table, new line (no dot) after.

    and it can be within brackets [name] and if it is the replace is the simplest replace [oldval] to [newval], if there is a bracket open before than there must be a bracket close after or if there is a bracket close after there must be a bracket open before.

    SELECT o.[name],
     o.name
     , o.Name 
     , o.Name 'test'
     ,o.namE[newName]
     FROM sys.objects o
    

    In our environment objects are not case sensitive, the replace needs to be case sensitive too.

    I also need to do the same for Table/View Names. This one also requires  case insensitive replace  if it is within brackets then before and after character must be within brackets [oldval] [newval], if it did not have brackets then there must be a dot in front and after it can be space or new line or tab or comma or end of text

    SELECT TOP 1 * FROM sys.objects o
    SELECT TOP 1 * FROM sys.[objects] o
    SELECT TOP 1 * FROM sys.[objects][o]
    SELECT TOP 1 * FROM sys.[objects]o
    SELECT TOP 1 * FROM sys.objects,sys.objects b
    

    To understand your regex I need to know more about it, do you recommend a reading about Regex?

    how does this line String revised = Regex.Replace(sb.ToString(), @"\b(a|TABLE)\.\[?(OldName|TESTVAL)(\]|\[|""|')?(id)?(\]|""|')?", MyReplace); relates to MyRefplace function I did not understand, are you passing parameters within a string?

    Thank you


    Gokhan Varol
    Friday, January 27, 2012 10:22 PM
  • I mistyped, the replace needs to be case insensitive.
    Gokhan Varol
    Friday, January 27, 2012 10:23 PM
  • for case insensitive, add (?i) at the beginning of the regex or use the RegexOptions.

    Best book is "Mastering Regular Expressions"

    The method Regex.Replace was in one of its overloads asking us for a delegate that points to a method. We created a method called MyReplace and had the delegate point to that method.


    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Friday, January 27, 2012 10:29 PM
  • how can I pass parameters to it not have the old value inside the regex string but use 

    Regex.Replace Method (String, String, MatchEvaluator)

     function instead?

    Gokhan Varol
    Friday, January 27, 2012 11:11 PM
  • Perhaps you are having difficulty understanding. The MatchEvaluator is a delegate that points to a method. In other words it is saying that you will handle the replacement in your own method that follows the MatchEvaluator signature. Which is a Match class in its parameter having the method return a string. What this allows you to do is go into greater specificity and hone the replacement in finer detail.

    So (String, String, YOUR own method to handle the replacement)

    So the parameter is asking which method will handle the replacement. In the example I gave, the MyReplace method will take care of that.


    John Grove, Senior Software Engineer http://www.digitizedschematic.com/

    • Edited by JohnGrove Monday, January 30, 2012 2:32 PM
    • Marked as answer by Paul Zhou Tuesday, February 07, 2012 4:57 AM
    Monday, January 30, 2012 2:31 PM