Regexp to get file extension

24 Aug 2014

Surprising, the amount of times I seem to do this:

# insert into t (filename) values ('blah.jar'), ('foo'), ('baz-1.2.0.jar'); commit;
INSERT 0 3
COMMIT
# select filename, case when regexp_replace(filename, E'^.*\\.', '') = filename then '' else regexp_replace(filename, E'^.*\\.', '') end as ext from t; rollback;
┌───────────────┬─────┐
│   filename    │ ext │
├───────────────┼─────┤
│ blah.jar      │ jar │
│ foo           │     │
│ baz-1.2.0.jar │ jar │
└───────────────┴─────┘