When cleaning up some old paperwork this weekend I stumbled over a very old tutorial on regular expressions. In fact, I received this little handout during a UNIX course I attended voluntarily during my first year at university. It seems that those two days really changed my life - the price tag: 100 Austrian Shillings which translates to something like 7 Euros in today's money.
Table of Contents
While looking over 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, unique way to transliterate the name of Libya's former dictator. According to this example there are around 30 ways to do it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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 couldn't resist trying it in PostgreSQL to see if things work just like 18 years ago.
~ 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 have 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:
|
1 2 |
test=# INSERT INTO t_code VALUES ('hans'); INSERT 0 1 |
|
1 2 3 4 5 6 7 8 9 10 |
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 that can be called a “safe investment”. Coding which still works after 20+ years without any changes can be considered good code!
Read more about coding topics in this blog about case-insensitive pattern matching by Laurenz Albe.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook, or LinkedIn.
Leave a Reply