Regexp_replace question / help needed

Christopher Molnar <cmolnar@ourworldservices.com> writes:

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&#39;,&#39;/[^/]*$&#39;,&#39;&amp;file=&#39;)

There's more than one way to do it. You could use capturing parens:

regexp_replace('http://test.com/test/testfile.php&#39;,&#39;/([^/]*)$&#39;,&#39;&amp;file=\1&#39;)

or you could use a lookahead constraint:

regexp_replace('http://test.com/test/testfile.php&#39;,&#39;/(?=[^/]*$)&#39;,&#39;&amp;file=&#39;)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,
I guess capture will help you look at
http://www.postgresql.org/docs/9.0/static/functions-matching.html

SELECT regexp_replace('http://test.com/test/testfile.php&#39;,
'^(.*)/(.*\.php)$', E'\\1&file=\\2', 'g')

2015-12-09 22:58 GMT+01:00 Christopher Molnar <cmolnar@ourworldservices.com>:

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&#39;,&#39;/[^/]*$&#39;,&#39;&amp;file=&#39;)

however this looses the file name and returns:

http://test.com/test&amp;file=

What I am looking for is:

http://test.com/test&amp;file=testfile.php

as a result.

Would anyone here point me in the right direction?

Thanks!
-Chris

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Christopher Molnar <cmolnar@ourworldservices.com> writes:

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&#39;,&#39;/[^/]*$&#39;,&#39;&amp;file=&#39;) 

however this looses the file name and returns:

http://test.com/test&amp;file=

What I am looking for is:

http://test.com/test&amp;file=testfile.php

as a result.

Would anyone here point me in the right direction?

select regexp_replace('http://foo/wow/blah/zzz.php&#39;, '/([^/]*)$', '&file=\1');

regexp_replace
----------------------------------
http://foo/wow/blah&amp;file=zzz.php
(1 row)

Thanks!
-Chris

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you both. Problem solved - worked perfectly.

On Wed, Dec 9, 2015 at 5:41 PM, Jerry Sievers <gsievers19@comcast.net>
wrote: