howardnews@xxxxxxxxxxxxx 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:]]*)$')
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
