From: | Howard Cole <howardnews(at)selestial(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Using regex to update a table |
Date: | 2010-01-05 16:16:44 |
Message-ID: | 4B4365EC.4000804@selestial.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
howardnews(at)selestial(dot)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:]]*)$')
From | Date | Subject | |
---|---|---|---|
Next Message | Milan Zamazal | 2010-01-05 16:20:25 | Re: Large tables, ORDER BY and sequence/index scans |
Previous Message | Pavel Stehule | 2010-01-05 16:03:47 | Re: Large tables, ORDER BY and sequence/index scans |