More RegEx Goodness

It's hard to believe that I have spent hours copying and pasting and manually reformatting text files when I had the power of regular expressions at my fingertips.  Oh, how true is the maximum "knowledge is power"

I had a need to create a text file with database names and key fields in a CSV format.  What I had was a text file with the database schema like this (I highlighted the parts of the text file I actually wanted.)

CREATE TABLE [dbo].[BuildCheck] (
[key] [uniqueidentifier] NOT NULL ,
[BuildStamp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[version] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[dtproperties] (
[id] [int] NOT NULL ,
[objectid] [int] NULL ,
[property] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uvalue] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lvalue] [image] NULL ,
[version] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblCountyNames] (
[pkey] [uniqueidentifier] NOT NULL ,
[intCountyNum] [int] NULL ,
[txtCountyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtpassword] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

What I wanted was this.

"BuildCheck","key"

"dtproperties", "id"

"tblCountyNames", "pkey"

So I turned again to the hidden power of Regular Expressions.  I crafted the following RegEx line
^(CREATE TABLE *)([\d\w\s\]\[\. ]*)( *\(\r\s*)(\[\w*\])([\w,\[\]\(\)\r\s]*Go)
and then did a search and replace, replacing the schema with the second and fourth (one based) items.  I know it looks hopelessly complex, but it's not.

Item One
^(CREATE TABLE *)
Matches a "CREATE TABLE" command at the beginning of the line, and a chain of spaces (represented by the space character and the asterisk, which tells the engine to repeat the previous match)

Item Two
([\d\w\s\]\[\. ]*)
Matches a block of numbers (/d) words (/w) and whitespace (/s), beginning and ending brackets and periods.  This translates to the database name

Item Three
(\s*\(\r\s*)
Match some whitespace, then a left parenthesis, then a carriage return following by a string of whitespace.

Item Four
(\[\w*\])
Item Four is our key field name.  We're looking for an opening and closing bracket with word characters inside.

Item Five
([\w,\[\]\(\)\r\s]*GO)
Item five is everything else up and including to the word GO.

Then, I did a search for
(^\[dbo\]\.\[)(\w*)(\]\s)(,)(\[)(\w*)(\])
Replacing it with
"\2","\6"
and viola!

Filling one copy of a database from another

Say you have two databases. Say that these two databases have a table with the same structure. Say you want to update table 1 with data from table 2. Easy cheesy.

INSERT INTO TableName

(Field1, Field2)

SELECT Field1, Field2

FROM Database.dbo.TableName AS SourceTableAlias

Regular Expressions

Here is what I love about regular expressions. They can make your life much easier.

I had to take a list of enumerated values, like this. (The list was a lot longer than shown)

Barbour=1,

Berkeley =2,

Boone =3,

Braxton =4,

Brooke =5,

Cabell =6,

Calhoun =7,

Clay =8,

Doddridge =9,

Fayette =10,

Gilmer =11,

Grant =12,

State=0,

Unknown = 99

and turn it into a switch statements in C# like this...

case 1:

value= LocationID.Barbour;

break;

case 2:

value= LocationID.Berkeley;

break;

case 3:

value= LocationID.Boone;

break;

case 4:

value= LocationID.Braxton;

break;

case 5:

value= LocationID.Brooke;

break;

case 6:

value= LocationID.Cabell;

break;

case 7:

value= LocationID.Calhoun;

break;

case 8:

value= LocationID.Clay;

break;

case 9:

value= LocationID.Doddridge;

break;

case 10:

value= LocationID.Fayette;

break;

case 11:

value= LocationID.Gilmer;

break;

case 12:

value= LocationID.Grant;

break;

case 0:

value= LocationID.State;

break;

case 99:

value= LocationID.Unknown;

break;

I did so using Edit Pad Pro and this search and replace code with Regular expressions

Search:

([a-zA-Z]*)( =)([0-9]*)

Replace:

case \3:

value= LocationID.\1;

break;

Dissecting it, [a-zA-Z] Matches all letters, both upper and lower case, while the asterisk at the end tells it to match all occurrences of it. So "a" and "aaaaaa" would both be a match. The [0-9]* does the same for the code number.

The parentheses separate the search into three terms. The replace using only terms 1 and 3 by specifying a backslash and the term I want (\1 for the first term, \3 for the third term).

The two big advantages are that I don't have to mess with copying and pasting values, which is tedious and time consuming, and I can be sure that I didn't paste the wrong value somewhere.