Re: regexp_replace question/suggestions needed - Mailing list pgsql-sql
Could you not use a simple capture and a greedy * ...
Should deal with your example
select regexp_replace('http://test.com/test/testfile.php','(.*)/(.*)','\1&file=\2');
and as requested <with different length and number of components>
select regexp_replace('http://test.com/otherstuff/in/path/test/testfile.php','(.*)/(.*)','\1&file=\2');
Hector Vass
07773 352 559
01666 820 008
MetaMetrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ
This e-mail and any attachments are confidential and for the attention of the addressee only. If you are not the intended recipient, any use, disclosure or copying of this document is unauthorised. If you have received this document in error please immediately notify the sender and delete this e-mail from your system. Whilst all emails sent by MetaMetrics are scanned using up-to-date virus scanning software, MetaMetrics Ltd. accepts no liability for any loss or damage which may be caused by software viruses and recommend that you conduct your own virus checks on all attached materials. Please note that any attached materials remain the exclusive property of MetaMetrics Ltd. unless expressly stated otherwise. Metametrics Limited is a limited company registered in England & Wales. Registered number 05453613. Registered offices at 86 Shirehampton Road, Stoke Bishop, Bristol, BS9 2DR
From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Corey Huinker <corey.huinker@gmail.com>
Sent: 14 December 2015 20:40
To: Christopher Molnar
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] regexp_replace question/suggestions needed
Sent: 14 December 2015 20:40
To: Christopher Molnar
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] regexp_replace question/suggestions needed
On Wed, Dec 9, 2015 at 4:57 PM, Christopher Molnar <cmolnar65@gmail.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:What I am looking for is:as a result.Would anyone here point me in the right direction?Thanks!-Chris
Not the most elegant, but this seems to do the trick
# select regexp_replace('http://test.com/test/testfile.php','/[^/]*$','') || '&file=' || regexp_replace('http://test.com/test/testfile.php','.*/','') ;
?column?
----------------------------------------
(1 row)