Using regex to update a table

howardnews@selestial.com wrote:

Happy new year everyone!

I am trying to create a column to extract the file extension from a
column in a table and set another column to this value. E.g. foo.doc
-> doc

I am using the regex E'\\.([[:alnum:]])*$', but I cannot seem to find
the correct update combination.

My best attempt to date is to use:

update test set extension = regexp_matches(filename,
E'\\.([[:alnum:]])*$')

However this does not effect columns where there is no match - ideally
I would want them setting to null or an empty string, also the
resultant text is of the format '{doc}' for example - as in a set or
similar.

Is there an alternative I can use to extract just the extension as a
string, and to update the non matching columns, setting them to null
or empty string?

Thanks.

Howard Cole
www.selestial.com

Aha! Found it:

update test set extension = substring(filename from E'\\.([[:alnum:]]*)$')