Обсуждение: Using regex to update a table

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

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:]]*)$')