>REGEX!W\

For a long time, I have been thinking about learning about of Regex, and today I just found the perfect excuse.

I have 277 strings like this:

ALTER TABLE Sampled_Strata_Vessels ADD CONSTRAINT DF_Sampled_Strata_Vessels_description DEFAULT(('missing')) FOR description

And I want to turn them into something like this:

ALTER TABLE Sampled_Strata_Vessels ALTER description SET DEFAULT ('missing');

(yes, I am converting constraints from SQL Server to PostgreSQL!)

I had a couple of options here:

– change them by hand (that is: 277 times!)

– write a C++ program to parse the strings and generate the results (or even better: a Python program!)

Somehow I did not felt like doing any of those things, so I decided to do something that took even longer, but hopefully made me gain something: learn how to do a regular expression and apply it in in Notepad++.

Regular expressions look both scary and magic;  it took me a couple of hours to understand what are groups, how to search for literals, etc.

In the end of the day I ended up with a smile on my face, and these two expressions:

search:

(ADD)(.*)(DEFAULT)(.*)(FOR)(.*)

replace:

ALTER \6 SET DEFAULT \4;

It looks stupidily easy, but I cannot express how usefull this sentences were for me, allowing me to modified 277 lines of text, in a milisecond!

The first expression parses my sentence and separates it into groups. The first group starts with the word “ADD”, then I have anything up to “Default”, the literal “default”, anything up to “FOR”, “For” itself and then the rest of the sentence (which is a last word). With these, I have all the blocks that I need to build my string.

So the replace expression starts just after ADD (it does not replace the beginning of the sentece) and I ask it to replace it by “ALTER” and the last group – the column name – (\6) then “set default” and the 4th group (\4), the default value.

That’s it 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s