Re: [GENERAL] regexp_replace double quote

Thank you! 2016-08-15 18:36 GMT+05:00, hubert depesz lubaczewski : > On Mon, Aug 15, 2016 at 06:27:06PM +0500, Михаил wrote: >> I need to escape double quotes only: >> test=# select regexp_replace('"""{Performer,"Boomwacker ""a"" >> Recording""}"""', '([^"])"{2}([^"])',

Re: [GENERAL] regexp_replace double quote

On Mon, Aug 15, 2016 at 9:27 AM, Михаил wrote: > Hi! > > I need to escape double quotes only: > test=# select regexp_replace('"""{Performer,"Boomwacker ""a"" > Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g'); > regexp_replace >

Re: [GENERAL] regexp_replace double quote

On Mon, Aug 15, 2016 at 06:27:06PM +0500, Михаил wrote: > I need to escape double quotes only: > test=# select regexp_replace('"""{Performer,"Boomwacker ""a"" > Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g'); > regexp_replace >

[GENERAL] regexp_replace double quote

Hi! I need to escape double quotes only: test=# select regexp_replace('"""{Performer,"Boomwacker ""a"" Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g'); regexp_replace - """{Performer,"Boomwacker \"a"" Recording\"}""" This is

[GENERAL] regexp_replace

Hi all. This is not doing as I'd expected: select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g'); regexp_replace 71096.013 (1 row) It acts the same with dashes: select regexp_replace('71-09-6-01-3', '(\d)[.-](\d)', '\1\2', 'g'); regexp_replace

Re: [GENERAL] regexp_replace

How about: select regexp_replace('71.09.6.01.3', '(\d)[.-]', '\1', 'g'); ? In your example, the (\d)[.-](\d) says find a digit followed by a period or dash followed by another digit. The first time through 1.0 is matched and replaced with 10 (710) with the "current location" pointing before the

Re: [GENERAL] regexp_replace

On 1/14/2016 1:59 PM, Tom Lane wrote: Andy Colson writes: This is not doing as I'd expected: select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g'); regexp_replace 71096.013 (1 row) I think regexp_replace considers only

Re: [GENERAL] regexp_replace

Andy Colson writes: > This is not doing as I'd expected: > select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g'); > regexp_replace > > 71096.013 > (1 row) I think regexp_replace considers only non-overlapping substrings, eg, once it's

Re: [GENERAL] regexp_replace

On Thu, Jan 14, 2016 at 12:43 PM, Andy Colson wrote: > Hi all. > > This is not doing as I'd expected: > > select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g'); > > regexp_replace > > 71096.013 > (1 row) > > ​Solution: select

Re: [GENERAL] regexp_replace

On 1/14/2016 2:02 PM, John McKown wrote: How about: select regexp_replace('71.09.6.01.3', '(\d)[.-]', '\1', 'g'); match is 1.3 and result is 13 ( 71096.013). If you don't want to eliminate the period or dash unless it is _between_ two digits, try: select regexp_replace('71.09.6.01.3',

Re: [GENERAL] regexp_replace

On 1/14/2016 2:06 PM, David G. Johnston wrote: select regexp_replace('71.09.6.01.3', '(\d)[.-](?=\d)', '\1\2', 'g'); Thanks David! -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] regexp_replace

On Thu, Jan 14, 2016 at 1:27 PM, Andy Colson wrote: > On 1/14/2016 2:06 PM, David G. Johnston wrote: > >> select regexp_replace('71.09.6.01.3', '(\d)[.-](?=\d)', '\1\2', 'g'); >> > ​John already picked up on the fact that the "\2" in the replacement is pointless (neither

[GENERAL] regexp_replace question / help needed

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

Re: [GENERAL] regexp_replace question / help needed

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 >

Re: [GENERAL] Regexp_replace question / help needed

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

Re: [GENERAL] Regexp_replace question / help needed

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

Re: [GENERAL] Regexp_replace question / help needed

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

[GENERAL] Regexp_replace question / help needed

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

Re: [GENERAL] Regexp_replace question / help needed

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

[GENERAL] regexp_replace to remove sql comments

Hi, I am trying to clean up the query field returned by the pg_stat_statements extension and remove all comments. Some of the queries in the query field contain comments like '-- some comment' and also '/* c style comments */' I have managed to strip off the '--' comments and also white

Re: [GENERAL] regexp_replace to remove sql comments

> >Von: pgsql-general-ow...@postgresql.org >[pgsql-general-ow...@postgresql.org] im Auftrag von Mike >[m...@wolman.co.uk] >Gesendet: Mittwoch, 28. Oktober 2015 20:04 >An: pgsql-general@postgresql.org >Betreff: [GENERAL] regexp_

Re: [GENERAL] regexp_replace to remove sql comments

Thanks with a bit of moving stuff about I think thats sorted it - in case anyone every needs it: SELECT query, trim(regexp_replace( regexp_replace( regexp_replace(query,'\/\*.+\*\/','','g'), '--[^\r\n]*', ' ', 'g') , '\s+', ' ', 'g')) as q FROM

Re: [GENERAL] regexp_replace to remove sql comments

Mike writes: > Thanks with a bit of moving stuff about I think thats sorted it - in > case anyone every needs it: >SELECT > query, > trim(regexp_replace( > regexp_replace( > regexp_replace(query,'\/\*.+\*\/','','g'), > '--[^\r\n]*',

Re: [GENERAL] regexp_replace failing on 9.0.4

On 03/18/2013 02:40 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine

Re: [GENERAL] regexp_replace failing on 9.0.4

Rob Sargent robjsarg...@gmail.com writes: For fun I decided to install 9.2 and thought I would try my luck there. Here's was I saw (apologies for the wide output). simple update in place update cms.segment_data set text = regexp_replace(text,'(^.*)ns/acres/pathology/dx/1.5(.*$)',

Re: [GENERAL] regexp_replace failing on 9.0.4

On 03/21/2013 06:25 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: For fun I decided to install 9.2 and thought I would try my luck there. Here's was I saw (apologies for the wide output). simple update in place update cms.segment_data set text =

[GENERAL] regexp_replace failing on 9.0.4

On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine This is may statement update cms.segment_data s set text = regexp_replace(s.text, '(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') from

Re: [GENERAL] regexp_replace failing on 9.0.4

Rob Sargent robjsarg...@gmail.com writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no changes affecting the regex code between 9.0.3 and 9.0.4. I'm suspicious that your

Re: [GENERAL] regexp_replace failing on 9.0.4

On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no changes affecting the regex code between 9.0.3

Re: [GENERAL] regexp_replace failing on 9.0.4

Rob Sargent robjsarg...@gmail.com writes: On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no

Re: [GENERAL] regexp_replace failing on 9.0.4

On 03/18/2013 02:40 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine

Re: [GENERAL] regexp_replace failing on 9.0.4

Maybe we're barking up the wrong tree by suspecting the regex itself. Perhaps the updates were suppressed by a trigger, or the transaction rolled back instead of committing, or some such? regards, tom lane Barking mad, more like it. I had rolled back the execution of

[GENERAL] regexp_replace puzzle

I've got a problem with regexp_replace which I could reduce to the following: CREATE FUNCTION digest(text, text) RETURNS bytea LANGUAGE c IMMUTABLE STRICT AS '$libdir/pgcrypto', 'pg_digest'; CREATE FUNCTION sha224enc(text) RETURNS text AS $$ BEGIN RAISE WARNING 'arg=»%«',

Re: [GENERAL] regexp_replace puzzle

2010/3/10 Harald Fuchs hari.fu...@gmail.com: I've got a problem with regexp_replace which I could reduce to the following:  CREATE FUNCTION digest(text, text) RETURNS bytea      LANGUAGE c IMMUTABLE STRICT      AS '$libdir/pgcrypto', 'pg_digest';  CREATE FUNCTION sha224enc(text) RETURNS

Re: [GENERAL] regexp_replace puzzle

On Wed, 10 Mar 2010 13:41:54 +0100, Harald Fuchs wrote about [GENERAL] regexp_replace puzzle: [snip] SELECT val, regexp_replace(val, '^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$', '\1' || '»\2«='|| sha224enc('\2') || '\3', 'i') [snip] i.e. the first '\2' gets

Re: [GENERAL] regexp_replace: LF, CR, or tab

Just for clarity: In the documentation (http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP) is mentioned Flag g causes the function to find each match in the string, not only the first one, and return a row for each such match. So in your example it was only

[GENERAL] regexp_replace: LF, CR, or tab

Hi! I am trying to replace characters '\r', '\n', or '\t' with space character ' '. As an example, I want string A\t\n\rB becomes AB. The following statement seems to be not working. What mistake have I made? TIA CN select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' ');

Re: [GENERAL] regexp_replace: LF, CR, or tab

2010/2/27 seil...@so-net.net.tw: Hi! I am trying to replace characters '\r', '\n', or '\t' with space character ' '. As an example, I want string A\t\n\rB becomes AB.  The following statement seems to be not working. What mistake have I made? TIA CN select

[GENERAL] regexp_replace - replace non-numbers

Hello everyone, I have problem with regexp_replace ( PostgreSQL 8.3.1). Please try this crappy string replacement: select regexp_replace('[EMAIL PROTECTED]^*()[]-=', '[^0-9]*' ,'') ..and I get: [EMAIL PROTECTED]^*()[]-= So only thirst assurance where removed, while documentation states that

Re: [GENERAL] regexp_replace - replace non-numbers

On Tue, Oct 28, 2008 at 10:42 AM, Vincas Dargis [EMAIL PROTECTED] wrote: Hello everyone, I have problem with regexp_replace ( PostgreSQL 8.3.1). Please try this crappy string replacement: select regexp_replace('[EMAIL PROTECTED]^*()[]-=', '[^0-9]*' ,'') ..and I get: [EMAIL

Re: [GENERAL] regexp_replace - replace non-numbers

Merlin Moncure wrote: On Tue, Oct 28, 2008 at 10:42 AM, Vincas Dargis [EMAIL PROTECTED] wrote: Hello everyone, I have problem with regexp_replace ( PostgreSQL 8.3.1). Please try this crappy string replacement: select regexp_replace('[EMAIL PROTECTED]^*()[]-=', '[^0-9]*' ,'') ..and I get:

[GENERAL] regexp_replace() [noindex] thing

Hi! I'm using tsearch2 and sometimes there are blocks of text that shouldn't be indexed. There is a trigger function that gathers data from the usual suspects and updates the index. in this trigger, I'd like to exclude thing in a [noindex] tag: select regexp_replace

Re: [GENERAL] regexp_replace() [noindex] thing

Marcus Engene [EMAIL PROTECTED] writes: I would like to have a function like the above that returns innan klas for this data. I would have expected it to as I use the non greedy version. regression=# select regexp_replace ('innan[noindex]apa[/noindex]klas[noindex]banan[/noindex]',

Re: [GENERAL] regexp_replace() [noindex] thing

Tom Lane wrote: Marcus Engene [EMAIL PROTECTED] writes: I would like to have a function like the above that returns innan klas for this data. I would have expected it to as I use the non greedy version. regression=# select regexp_replace

Re: [GENERAL] REGEXP_REPLACE woes

For the record: I've got two different flavors of those shortlinks. The first one, [p=123|John Smith] is the one that I started this thread with. The second one is just a person number like [p=123] and should be expanded to a similar link, with the default person name (fetched by

Re: [GENERAL] REGEXP_REPLACE woes

On Wednesday 11. June 2008, Leif B. Kristensen wrote: p := BTRIM(tmp, '#')::INTEGER; name := get_person_name(p); str := REPLACE(str, tmp, name); I did some folding and replaced the above with str := REPLACE(str, tmp, get_person_name(BTRIM(tmp, '#')::INTEGER)); and got

[GENERAL] REGEXP_REPLACE woes

I want to transform the text '[p=1242|John Smith]' to a href=./family.php?person=1242John Smith/a, but what I get is: pgslekt= select REGEXP_REPLACE('[p=1242|John Smith]', pgslekt( E'[p=(\d+)|(.+?)]', pgslekt( E'a href=./family.php?person=\\1\\2/a'); regexp_replace

Re: [GENERAL] REGEXP_REPLACE woes

On Tue, Jun 10, 2008 at 01:28:06PM +0200, Leif B. Kristensen wrote: I want to transform the text '[p=1242|John Smith]' to a href=./family.php?person=1242John Smith/a, but what I get is: pgslekt= select REGEXP_REPLACE('[p=1242|John Smith]', pgslekt( E'[p=(\d+)|(.+?)]', pgslekt( E'a

Re: [GENERAL] REGEXP_REPLACE woes

On Tuesday 10. June 2008, Michael Fuhr wrote: Parts of the regular expression need more escaping.  Try this: select regexp_replace(    '[p=1242|John Smith]',   e'\\[p=(\\d+)\\|(.+?)\\]',   e'a href=./family.php?person=\\1\\2/a' );                   regexp_replace

Re: [GENERAL] REGEXP_REPLACE woes

I put the code into a function, link_expand(): CREATE OR REPLACE FUNCTION link_expand(TEXT) RETURNS TEXT AS $$ SELECT REGEXP_REPLACE($1, E'\\[p=(\\d+)\\|(.+?)\\]', E'a href=./family.php?person=\\1\\2/a', 'g'); $$ LANGUAGE sql STABLE; pgslekt= select

Re: [GENERAL] REGEXP_REPLACE woes

On Tue, Jun 10, 2008 at 02:25:44PM +0200, Leif B. Kristensen wrote: Thank you Michael, I figured it was something fishy with the escaping. When I try your example, I get pgslekt= select regexp_replace( pgslekt(    '[p=1242|John Smith]', pgslekt(   e'\\[p=(\\d+)\\|(.+?)\\]', pgslekt(   e'a

Re: [GENERAL] REGEXP_REPLACE woes

On Tue, Jun 10, 2008 at 02:59:53PM +0200, Leif B. Kristensen wrote: So far, so good. But look here: pgslekt= select link_expand('[p=123|John Smith] and [p=456|Jane Doe]'); link_expand --- a

Re: [GENERAL] REGEXP_REPLACE woes

On Tuesday 10. June 2008, Leif B. Kristensen wrote: Hey, I told it not to be greedy, didn't I? Found it. I must make *both* atoms non-greedy: pgslekt= CREATE OR REPLACE FUNCTION link_expand(TEXT) RETURNS TEXT AS $$ SELECT REGEXP_REPLACE($1, E'\\[p=(\\d+?)\\|(.+?)\\]',

Re: [GENERAL] REGEXP_REPLACE woes

On Tue, Jun 10, 2008 at 07:41:53AM -0600, Michael Fuhr wrote: On Tue, Jun 10, 2008 at 02:59:53PM +0200, Leif B. Kristensen wrote: So far, so good. But look here: pgslekt= select link_expand('[p=123|John Smith] and [p=456|Jane Doe]'); link_expand

Re: [GENERAL] REGEXP_REPLACE woes

On Tuesday 10. June 2008, CaT wrote: On Tue, Jun 10, 2008 at 03:43:02PM +0200, Leif B. Kristensen wrote: On Tuesday 10. June 2008, Leif B. Kristensen wrote: Hey, I told it not to be greedy, didn't I? Found it. I must make *both* atoms non-greedy: That makes no sense. Take this bit of perl,

Re: [GENERAL] REGEXP_REPLACE woes

On Tue, Jun 10, 2008 at 03:43:02PM +0200, Leif B. Kristensen wrote: On Tuesday 10. June 2008, Leif B. Kristensen wrote: Hey, I told it not to be greedy, didn't I? Found it. I must make *both* atoms non-greedy: That makes no sense. Take this bit of perl, which works as expected: $str =

Re: [GENERAL] REGEXP_REPLACE woes

CaT [EMAIL PROTECTED] writes: There's a bug in your version of pcre I think as postgres would have little to do with the regex itself (I be guessing). You be wrong ... PG uses Tcl's regex engine, not pcre, and this behavior is as documented. No, I don't know why Henry Spencer chose to do it

Re: [GENERAL] REGEXP_REPLACE woes

On Tuesday 10. June 2008, Michael Fuhr wrote: Something between my message and your shell appears to have converted a few spaces to no-break spaces. A hex dump of your query shows the following: 73 65 6c 65 63 74 20 72 65 67 65 78 70 5f 72 65 |select regexp_re| 0010 70 6c 61

Re: [GENERAL] regexp_replace in two times?

On Thursday 8 May 2008 Tom Lane's cat, walking on the keyboard, wrote: Maybe the original strings had more than one instance of 'TIF'? Opsyou're right, I've checked with a backup copy and I found four records with the double tif pattern. I should have get it beforesorry! Luca -- Sent

[GENERAL] regexp_replace in two times?

Hi all, I used the regexp_replace function to make a substitution over a table, but I got a strange behaviour (please consider I'm not an expert of regex). The idea is to remove the final part of a code, that could be TIF, ISTTIF, tif, isttif, and at the same time consider only the records

Re: [GENERAL] regexp_replace in two times?

Luca Ferrari [EMAIL PROTECTED] writes: I used the regexp_replace function to make a substitution over a table, but I got a strange behaviour (please consider I'm not an expert of regex). The idea is to remove the final part of a code, that could be TIF, ISTTIF, tif, isttif, and at the same

Re: [GENERAL] regexp_replace() function in new version

Abhijeet [EMAIL PROTECTED] writes: I have tried following regex in function: - SELECT regexp_replace('iAbhijeet/b', '(\s)*/?(?i:script|i|b|u|embed|object|a|frameset|frame|iframe|meta|link|style|table|th|td|tr|tbody|input|select|option|form|map|area|!--)(.|\n)*?', '\\s'); I think

[GENERAL] regexp_replace() function in new version

Hi, regexp_replace() function in new version of PostgreSQL is giving error. I am trying to remove tags from string. I have tried following regex in function: - SELECT regexp_replace('iAbhijeet/b',

Re: [GENERAL] regexp_replace() function in new version

On Nov 14, 2007 7:53 AM, Abhijeet [EMAIL PROTECTED] wrote: Hi, regexp_replace() function in new version of PostgreSQL is giving error. I am trying to remove tags from string. I have tried following regex in function: SELECT regexp_replace('iAbhijeet/b',

[GENERAL] regexp_replace

Hi all, I would like to change a sub-string in a text-field by using UPDATE tablename SET fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string) WHERE (fieldname like '%old_sub_string%') In priniciple, it works. However, only one occurence of old_sub_string is replaced and further

Re: [GENERAL] regexp_replace

am Mon, dem 23.07.2007, um 7:50:35 -0700 mailte [EMAIL PROTECTED] folgendes: Hi all, I would like to change a sub-string in a text-field by using UPDATE tablename SET fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string) WHERE (fieldname like '%old_sub_string%') In

Re: [GENERAL] regexp_replace

On Mon, Jul 23, 2007 at 07:50:35AM -0700, [EMAIL PROTECTED] wrote: I would like to change a sub-string in a text-field by using UPDATE tablename SET fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string) WHERE (fieldname like '%old_sub_string%') In priniciple, it works.

Re: [GENERAL] regexp_replace

On Jul 23, 2007, at 9:50 AM, [EMAIL PROTECTED] wrote: fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string) Add a 'g' flag: fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string, 'g') From the end of a paragraph in 9.7.3 just before 9.7.3.1: The flags parameter is an