[GENERAL] regexp_replace question / help needed

[Christopher Molnar]

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 "=" while preserving the filename and
extension following the "/".

The closest I can get is:

regexp_replace('http://test.com/test/testfile.php','/[^/]*$','=')

however this looses the file name and returns:

http://test.com/test=

What I am looking for is:

http://test.com/test=testfile.php

as a result.

Would anyone here point me in the right direction?

Thanks!
-Chris

Re: [GENERAL] regexp_replace question / help needed

[David Rowley]

On 10 December 2015 at 10:56, Christopher Molnar 
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 "=" while preserving the filename and
> extension following the "/".
>
> The closest I can get is:
>
> regexp_replace('http://test.com/test/testfile.php','/[^/]*$','=')
>
> however this looses the file name and returns:
>
> http://test.com/test=
>
> What I am looking for is:
>
> http://test.com/test=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'=\\1');

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

Re: [GENERAL] Regexp_replace question / help needed

[Nicolas Paris]

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',
'^(.*)/(.*\.php)$', E'\\1=\\2', 'g')

2015-12-09 22:58 GMT+01:00 Christopher Molnar :
> 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 "=" while preserving the filename and extension
> following the "/".
>
> The closest I can get is:
>
> regexp_replace('http://test.com/test/testfile.php','/[^/]*$','=')
>
> however this looses the file name and returns:
>
> http://test.com/test=
>
> What I am looking for is:
>
> http://test.com/test=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

Re: [GENERAL] Regexp_replace question / help needed

[Tom Lane]

Christopher Molnar  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 "=" while preserving the filename and
> extension following the "/".
> The closest I can get is:
> regexp_replace('http://test.com/test/testfile.php','/[^/]*$','=')

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

regexp_replace('http://test.com/test/testfile.php','/([^/]*)$','=\1')

or you could use a lookahead constraint:

regexp_replace('http://test.com/test/testfile.php','/(?=[^/]*$)','=')

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

Re: [GENERAL] Regexp_replace question / help needed

[Jerry Sievers]

Christopher Molnar  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
> "=" while preserving the filename and extension following the "/".
>
> The closest I can get is:
>
> regexp_replace('http://test.com/test/testfile.php','/[^/]*$','=') 
>
> however this looses the file name and returns:
>
> http://test.com/test=
>
> What I am looking for is:
>
> http://test.com/test=testfile.php
>
> as a result.
>
> Would anyone here point me in the right direction?


> select regexp_replace('http://foo/wow/blah/zzz.php', '/([^/]*)$', '=\1');
  regexp_replace  
--
 http://foo/wow/blah=zzz.php
(1 row)


>
> Thanks!
> -Chris
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@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

[GENERAL] Regexp_replace question / help needed

[Christopher Molnar]

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 "=" while preserving the filename and
extension following the "/".

The closest I can get is:

regexp_replace('http://test.com/test/testfile.php','/[^/]*$','=')

however this looses the file name and returns:

http://test.com/test=

What I am looking for is:

http://test.com/test=testfile.php

as a result.

Would anyone here point me in the right direction?

Thanks!
-Chris

Re: [GENERAL] Regexp_replace question / help needed

[Christopher Molnar]

Thank you both. Problem solved - worked perfectly.

On Wed, Dec 9, 2015 at 5:41 PM, Jerry Sievers 
wrote:

> Christopher Molnar  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
> > "=" while preserving the filename and extension following the "/".
> >
> > The closest I can get is:
> >
> > regexp_replace('http://test.com/test/testfile.php','/[^/]*$','=')
> >
> > however this looses the file name and returns:
> >
> > http://test.com/test=
> >
> > What I am looking for is:
> >
> > http://test.com/test=testfile.php
> >
> > as a result.
> >
> > Would anyone here point me in the right direction?
>
>
> > select regexp_replace('http://foo/wow/blah/zzz.php', '/([^/]*)$',
> '=\1');
>   regexp_replace
> --
>  http://foo/wow/blah=zzz.php
> (1 row)
>
>
> >
> > Thanks!
> > -Chris
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consult...@comcast.net
> p: 312.241.7800
>