Monday, 28 April 2008

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 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:




dbo.OpportunityRawImport_Stage1Load as Stage1


dbo.OpportunityRawImport_Stage2Load as Stage2


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: 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)

No comments: