Using Regular Expressions for Simple Code Generation in UltraEdit

I love getting tools to do repetitive 'monkey work' for me. To this end, there is an alternative to my post on code generation via sysobjects at http://ddkonline.blogspot.com/2008/02/rehash-daves-simple-sql-workbenchquery.html. Difference in this post that is code/SQL generation is done through… you guessed it… UltraEdit Regular Expressions!

My task – I wanted to do a multiple column join to determine if any updates had been made to a table (compared to a table which contained the original Excel import). Now instead of doing the hard work and going through all 70 columns and manually typing each table name in e.g. Table1.ColumnName = Table2.Column name for every column, I used the following shortcut:

1. Drag the columns folder from the treeview to the working area in SQL 2005 WorkBench so you have a list of comma-separated columns (e.g. RecordNumber, Information, RecordType, DocAuthor)
   
   
   



2. Copy this into my the text editor, UltraEdit (make sure you have Advanced -> Configuration -> Search -> Regular Expression Engine set to “Unix Style” as used in .NET rather than “Ultra Edit Style”)




3. Ctrl+R to open the replace dialog and put in “\W*(\w*),” to the Find box and “Table1.\1 = Table2.\1 AND\n” in the Replace Box.
   
   
   



4. Do the replace and you will now have a properly formatted join expression for use in your queries. i.e.
   
   Table1.RecordNumber = Table2.RecordNumber AND
   Table1.Information = Table2.Information AND
   Table1.RecordType = Table2.RecordType,

You can now put the joins back into your query:

    SELECT

        *

    FROM

        dbo.OpportunityRawImport_Stage1Load as Stage1

    INNER JOIN

        dbo.OpportunityRawImport_Stage2Load as Stage2

    ON

        Stage1.RecordNumber = Stage2.RecordNumber AND

        Stage1.Information = Stage2.Information AND

        Stage1.RecordType = Stage2.RecordType AND

Handy Tools for Building Regular Expressions:

A free regular expression editor with English interpretations of your regular expressions (The Regular Expression Workbench) can be found at:
http://blogs.msdn.com/ericgu/archive/2003/07/07/52362.aspx. For example, a regular expression for Australian Postcodes ("^[\d]{4,4}$". Is interpreted as:

^ (anchor to start of string)
Any character in "\d"
At least 4, but not more than 4 times
$ (anchor to end of string)