When cleaning up some old paperwork this weekend I stumbled over a very old tutorial. In fact, I have received this little handout during a UNIX course I attended voluntarily during my first year at university. It seems that these two days have really changed my life – the price tag: 100 Austrian Schillings which translates to something like 7 Euros in today’s money.
When looking at this old thing I noticed a nice example showing how to test regular expression support in grep. Over the years I had almost forgotten this little test. Here is the idea: There is no single way to print the name of Libya’s former dictator. According to this example there are around 30 ways to do it:
test=# CREATE TABLE t_code (name text); CREATE TABLE test=# Copy t_code FROM stdin; Gadaffi Gadafi Gadafy Gaddafi Gaddafy Gaddhafi Gadhafi Gathafi Ghadaffi Ghadafi Ghaddafi Ghaddafy Gheddafi Kadaffi Kadafi Kaddafi Kadhafi Kazzafi Khadaffy Khadafy Khaddafi Qadafi Qaddafi Qadhafi Qadhdhafi Qadthafi Qathafi Quathafi Qudhafi Kad'afi
Of course I could not resist to try it in PostgreSQL and see if things work just like 18 years ago. In PostgreSQL there are a handful of operators to work with regular expressions:
~ Matches regular expression, case sensitive
~* Matches regular expression, case insensitive
!~ Does not match regular expression, case sensitive
!~* Does not match regular expression, case insensitive
In our case we got a case sensitive regular expression so that ~ operator should work. To make sure we get the right result, we will add one more row to the data set:
test=# INSERT INTO t_code VALUES ('hans'); INSERT 0 1
Let us give it a try now:
test=# SELECT name, name ~ '(Kh?|Gh?|Qu?)[aeu](d[''dt]?|t|zz|dhd)h?aff?[iy]' FROM t_code; name | ?column? -----------+---------- Gadaffi | t Gadafi | t ... Qudhafi | t Kad'afi | t hans | f (31 rows)
WOW 🙂 Things work just like 20 years ago – without any changes, without a single problem. I guess this can be called a “safe investment”. Coding, which is working for 20+ years without any changes, can be considered as good code …