One solution is to use regexps to remove that part of the filename. Assuming the separators are known
regexp_replace(filename, '^(.*?)[-_][rR][0-9]+(\.[^.]+)$', E'\\1\\2')
This would assume separators - or _, then r or R followed by at least one number, a dot and then something but never more dots. The part with r/R+numers would be removed.
The efficiency of this depends on the amount of data and how the searches are done. It could be used in an index to speed up searching. For example
CREATE INDEX filename_name_idx ON documents (regexp_replace(filename, '^(.*?)[-_][rR][0-9]+(\.[^,]+)', E'\\1\\2'));
Then doing a search
SELECT filename FROM documents
WHERE regexp_replace(filename,
'^(.*?)[-_][rR][0-9]+(\.[^,]+)', E'\\1\\2') = 'LLE-MET-AP-0000-PLA-COB.pdf';
would use index (if the query optimizer deems it faster).
Do note that the regexp part has to be exactly the same in the index and the search, otherwise the index cannot be used. You can also combine it with LOWER()
if case insensitive searching is required.
The same regexp can be used to remove the revision identifier from the search string also, if there is a need to search for matching files for, e.g., LLE-MET-AP-0000-PLA-COB-R00.pdf
Making a view with this would make it a lot nicer to use and the actual regexp would be in the database and not in the application layer.
My own solution so far is to paste the string literal into the query:
EXECUTE format(
'UPDATE %I SET %I = ' || quote_literal(newvalue) || ' WHERE %I = $1 ',
relname, colname, relname || '_id') USING row_id;
or just
EXECUTE format(
'UPDATE %I SET %I = %L WHERE %I = $1',
relname, colname, newvalue, relname || '_id') USING row_id;
This works for, e.g., appropriately formatted date types ('1990-05-04'
). Probably this sacrifices being able to re-use query plan.
Best Answer
The
SUBSTRING
function has regex support, so the following would work:The so-called capturing group
()
is to make sure you capture only the shipment ID and not the entire string "Delayed 12345 for Carrier". The?
makes the capture non-greedy, to make sure the first 'for Carrier' is matched.