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