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