I had a lot of fun the other day trying to parse an address from a single line format "John Smith, Marketing Department, 6843 Rouge Rue, Salina, KS 67421" into a set of dedicated columns for each part of the address. As you can see, most of the sting is separated by commas, but the state and ZIP code are separated only by space. Of course I wanted to use regular expressions to parse that, but that turned out to be an interesting problem to solve. The problem was that regexp_matches() function returns setof(string[]) as its result and the query to move those parts into separate columns turned out to be far from trivial.
Here it is:
insert into addresses(address1, address2, city, state, zip)
select addr[1], addr[2], addr[3], addr[4], addr[5] from
(select regexp_matches(fullAddress,
'[^,]*,\s*([^,]*),\s*([^,]*,[^,]*),\s*([^,]*),\s*([^\s]*)\s*(.*)') as addr
from mytable where fullAddress is not null as subquery1;
That is a little more verbose that I'd like it to be, but it works. And, hey, not every database would even have such great support for regular expression!