Re: regexp_replace question / help needed

On 10 December 2015 at 10:56, Christopher Molnar <cmolnar65(at)gmail(dot)com>
wrote:

> Hello,
>
> I am running into a problem and need some pointers on regexp_replace - I
> can't seem to find an answer in any of the online resources.
>
> I have a string (like 40,000 with different length and number of
> components) of them in a field named "externalurl". I need to replace the
> final "/" of the string with "&file=" while preserving the filename and
> extension following the "/".
>
> The closest I can get is:
>
> regexp_replace('http://test.com/test/testfile.php','/[^/]*$','&file=')
>
> however this looses the file name and returns:
>
> http://test.com/test&file=
>
> What I am looking for is:
>
> http://test.com/test&file=testfile.php
>
> as a result.
>
> Would anyone here point me in the right direction?
>

I think you're just missing the capture group to grab the filename. What
you capture in the group is then available to use as you please in \1
(which needs be escaped as \\1) so something like regexp_replace('
http://test.com/test/testfile.php','/([^/]*$)', E'&file=\\1');

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services