RegEx in Dot Net

While attempting to parse a QuickBooks export file, I had need to match a rather complicate pattern.  The pattern was easy do express in Regular Expressions, but not so much with normal string handling in Dot Net.

Proving that everything and the kitchen sink is available in the Dot Net Framework, here is how you can use the beauty of Regular Expressions in Visual Basic 2008.

Imports    System.Text.RegularExpressions.
sub amatch
 dim aMatch = Regex.Match(InputField, ":[0-9A-Za-z]* " & Chr(&HB7))
if aMatch.Success then
   'Do Something
end if

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.