Re: [SQL] regexp_replace behavior

[Marcin Krawczyk]

Yes that's exactly what I needed. Thanks a lot.

pozdrowienia
mk


2012/11/20 Alvaro Herrera 

> Marcin Krawczyk escribió:
> > Hi list,
> >
> > I'm trying to use regexp_replace to get rid of all occurrences of
> > certain sub strings from my string.
> > What I'm doing is:
> >
> > SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H
> > {tt}{POL23423423}', E'\{.+\}', '', 'g')
> >
> > so get rid of whatever is between { } along with these,
> >
> > but it results in:
> > 'F0301 305-149-101-0 F0302 '
> >
> > how do I get it to be:
> > 'F0301 305-149-101-0 F0302 12W47 0635H'
> >
> > ??
> >
> > as I understood the docs, the g flag "specifies replacement of each
> > matching substring rather than only the first one"
>
> The first \{.+\} match starts at the first { and ends at the last },
> eating the {s and }s in the middle.  So there's only one match and that's
> what's removed.
>
> > what am I missing ?
>
> You need a non-greedy quantifier.  Try
>
>  SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H
> {tt}{POL23423423}', E'\{.+?\}', '', 'g')
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [SQL] regexp_replace behavior

[Alvaro Herrera]

Marcin Krawczyk escribió:
> Hi list,
> 
> I'm trying to use regexp_replace to get rid of all occurrences of
> certain sub strings from my string.
> What I'm doing is:
> 
> SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H
> {tt}{POL23423423}', E'\{.+\}', '', 'g')
> 
> so get rid of whatever is between { } along with these,
> 
> but it results in:
> 'F0301 305-149-101-0 F0302 '
> 
> how do I get it to be:
> 'F0301 305-149-101-0 F0302 12W47 0635H'
> 
> ??
> 
> as I understood the docs, the g flag "specifies replacement of each
> matching substring rather than only the first one"

The first \{.+\} match starts at the first { and ends at the last },
eating the {s and }s in the middle.  So there's only one match and that's
what's removed.

> what am I missing ?

You need a non-greedy quantifier.  Try

 SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H 
{tt}{POL23423423}', E'\{.+?\}', '', 'g')

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


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

[SQL] regexp_replace behavior

[Marcin Krawczyk]

Hi list,

I'm trying to use regexp_replace to get rid of all occurrences of
certain sub strings from my string.
What I'm doing is:

SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H
{tt}{POL23423423}', E'\{.+\}', '', 'g')

so get rid of whatever is between { } along with these,

but it results in:
'F0301 305-149-101-0 F0302 '

how do I get it to be:
'F0301 305-149-101-0 F0302 12W47 0635H'

??

as I understood the docs, the g flag "specifies replacement of each
matching substring rather than only the first one"
what am I missing ?

regards
mk

Re: [SQL] regexp_replace and search/replace values stored in table

[Sofer, Yuval]

Hi 

Postgres crashes with -

PG "FATAL:  could not reattach to shared memory (key=5432001, addr=0210): 
Invalid argument.

The version is 8.2.4, the platform is win32

Does someone know the reason/workaround ? 

Thanks, 
Yuval Sofer
BMC Software
CTM&D Business Unit
DBA Team
972-52-4286-282
yuval_so...@bmc.com



-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Leif Biberg Kristensen
Sent: Saturday, May 01, 2010 8:29 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] regexp_replace and search/replace values stored in table

Followup II: I've written a blog post on the subject at 
<http://solumslekt.org/blog/?p=151>.
 
regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/

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


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

Re: [SQL] regexp_replace and search/replace values stored in table

[Leif Biberg Kristensen]

Followup II: I've written a blog post on the subject at 
.
 
regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/

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

Re: [SQL] regexp_replace and search/replace values stored in table

[Leif Biberg Kristensen]

Followup. Replaced Big Ugly Function with:

CREATE OR REPLACE FUNCTION _my_expand(TEXT) RETURNS TEXT AS $$
-- private func, expand various compact links
DECLARE
str TEXT = $1;
links RECORD;

BEGIN
FOR links IN SELECT short_link, long_link FROM short_links LOOP
str := REGEXP_REPLACE(str, links.short_link, links.long_link, 'g');
END LOOP;
RETURN str;
END
$$ LANGUAGE plpgsql IMMUTABLE;

By the way, those who haven't read this gem should probably do so: 


regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/

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

Re: [SQL] regexp_replace and search/replace values stored in table

[Leif Biberg Kristensen]

On Tuesday 27. April 2010 13.43.48 Leif Biberg Kristensen wrote:
> CREATE TABLE short_links (
> link_type CHAR(2) PRIMARY KEY,
> short_link TEXT,
> long_link TEXT,
> description TEXT
> );
> 
> It appears like I have to double the number of backslashes when I enter the 
> data:
> 
> INSERT INTO short_links (link_type, short_link, long_link, description) 
VALUES
> ('sk', E'[sk=(.+?)|(.+?)|(.+?)]',
> E'3',
> 'Scanned probate registers [sk=protocol|image reference|link text]');

I found the solution. I was totally wrong about the 4 backslashes.

pgslekt=> delete from short_links;
DELETE 1
pgslekt=> INSERT INTO short_links (link_type, short_link, long_link, 
description) VALUES
pgslekt-> ('sk', E'\\[sk=(.+?)\\|(.+?)\\|(.+?)\\]',
pgslekt(> E'\\3',
pgslekt(> 'Scanned probate registers [sk=protocol|image reference|link 
text]');
INSERT 0 1
pgslekt=> select regexp_replace((select source_text from sources where 
source_id=23091), (select short_link from  long_link from short_links where 
link_type = 'sk'), 'g');


 regexp_replace  
-
 side 66a. Vabakken under Klyve vestre i Solum 
07.07.1784: [p=6947|Isach Jonsen].
(1 row)

Beautiful. Now I'm going replace this big ugly function with a loop reading 
values from a table. I may even write an interface for managing shortlinks :)

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/

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

Re: [SQL] regexp_replace and search/replace values stored in table

[Leif Biberg Kristensen]

On Tuesday 27. April 2010 15.04.23 Tim Landscheidt wrote:
> Leif Biberg Kristensen  wrote:
> 
> > [...]
> > So far, so good. But when I try to do the actual expansion, I'm stumped.
> 
> > pgslekt=> select regexp_replace((select source_text from sources where
> > source_id=23091), (select quote_literal(short_link) from short_links where
> > link_type = 'sk'), (select quote_literal(long_link) from short_links where
> > link_type = 'sk'), 'g');
> >  regexp_replace
> > 

> >  [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784:
> > [p=6947|Isach Jonsen].
> > (1 row)
> 
> > What am I missing?
> 
> For starters, omit the call to quote_literal ().
> 
> Tim

Tim,
that was actually the first thing i tried, but I omitted it from the post:

pgslekt=> select regexp_replace((select source_text from sources where 
source_id=23091), (select short_link from short_links where link_type = 'sk'), 
(select long_link from short_links where link_type = 'sk'), 'g');
 regexp_replace

 [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784: 
[p=6947|Isach Jonsen].
(1 row)

It doesn't work either.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/

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

Re: [SQL] regexp_replace and search/replace values stored in table

[Tim Landscheidt]

Leif Biberg Kristensen  wrote:

> [...]
> So far, so good. But when I try to do the actual expansion, I'm stumped.

> pgslekt=> select regexp_replace((select source_text from sources where
> source_id=23091), (select quote_literal(short_link) from short_links where
> link_type = 'sk'), (select quote_literal(long_link) from short_links where
> link_type = 'sk'), 'g');
>  regexp_replace
> 
>  [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784:
> [p=6947|Isach Jonsen].
> (1 row)

> What am I missing?

For starters, omit the call to quote_literal ().

Tim


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

[SQL] regexp_replace and search/replace values stored in table

[Leif Biberg Kristensen]

I've got a system for entering and storing a lot of standard hyperlinks in a 
compact form, and then expand them at run time like this:

CREATE OR REPLACE FUNCTION _my_expand(TEXT) RETURNS TEXT AS $$
-- private func, expand various compact links
DECLARE
str TEXT = $1;
BEGIN
-- Scanned church books [kb=book reference|image reference|link text]
str := REGEXP_REPLACE(str, E'\\[kb=(.+?)\\|(.+?)\\|(.+?)\\]',
E'\\3', 'g');
-- Scanned probate registers [sk=protocol|image reference|link text]
str := REGEXP_REPLACE(str, E'\\[sk=(.+?)\\|(.+?)\\|(.+?)\\]',
E'\\3', 'g');
-- Scanned deed ("pantebok") registers [sk=protocol|image reference|link 
text]
str := REGEXP_REPLACE(str, E'\\[tl=(.+?)\\|(.+?)\\|(.+?)\\]',
E'\\3', 'g');
RETURN str;
END
$$ LANGUAGE plpgsql STABLE;

According to the slogan "minimize code, maximize data" I feel that these 
strings should be stored in a table:

CREATE TABLE short_links (
link_type CHAR(2) PRIMARY KEY,
short_link TEXT,
long_link TEXT,
description TEXT
);

It appears like I have to double the number of backslashes when I enter the 
data:

INSERT INTO short_links (link_type, short_link, long_link, description) VALUES
('sk', E'[sk=(.+?)|(.+?)|(.+?)]',
E'3',
'Scanned probate registers [sk=protocol|image reference|link text]');

pgslekt=> select * from short_links;

 link_type |   short_link   |   
  
long_link | 
   
description
---++---+---
 sk| \\[sk=(.+?)\\|(.+?)\\|(.+?)\\] | \\3 | Scanned probate registers [sk=protocol|image 
reference|link text]
(1 row)

So far, so good. But when I try to do the actual expansion, I'm stumped.

pgslekt=> select regexp_replace((select source_text from sources where 
source_id=23091), (select quote_literal(short_link) from short_links where 
link_type = 'sk'), (select quote_literal(long_link) from short_links where 
link_type = 'sk'), 'g');
 regexp_replace

 [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784: 
[p=6947|Isach Jonsen].
(1 row)

What am I missing?

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/

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

Re: [SQL] regexp_replace and UTF8

[Jasen Betts]

On 2009-02-02, Bart Degryse  wrote:
>
> --=__PartF6DE34E1.0__=
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> Thanks for the ideas!
> The function Jasen suggests works partially.
> There are also entities like
> ‘
> ’
> “
> ”
> …

these work fine here.

jasen=# select htmlent('‘ ’ “ ” …');
  htmlent  
  ---
   ‘ ’ “ ” …
   (1 row)
   
> The output of the htmlent function for these looks like |

I see a pipe symbol '|' is that what you wrote?

> Was that what you meant with 
>>"characters outside of the LATIN-1 space
>> are not handled but apparently this is what you want." ?

I was under a mistaken impression of the encoding of HTML numeric
entities.

> Because in that case, they should be handled too.
> How should that be done.
> Thanks,

it works here because server_encoding is UTF8;

to check do this.

  show server_encoding;show client_encoding;
  
it looks like you's need to convert the numbers to utt-8 in a bytea
type and then use convert to translate then to your preferred encoding
(appears to be win125x)

postgresql8.3 appears to provide no way to generate UTF-8 in a bytea
type so if you can't use a utf-8 encoding for your database you'll
have to write your own.








 Jasen Betts  2009-01-31 12:47 >>>
> On 2009-01-30, Bart Degryse  wrote:
>>
>> --=3D__Part8EA648F8.0__=3D
>> Content-Type: text/plain; charset=3DUTF-8
>> Content-Transfer-Encoding: quoted-printable
>>
>> Hi,
>> I have a text field with data like this: 'de patiënt niet'
>> (without the quotes).
>> I would like to convert this string to look like this: 'de pati=C3=ABnt
>> niet'
>> Basically what I need to do (I think) is
>> - get rid of the &, # and ;
>> - convert the number to hex
>> - make a UTF8 from that (thus: \xEB)
> that is not UTF8.
>
> the UTF8 representation for '=C3=AB' in  SQL is e'\xC3\xAB' or chr(235)
>
> your input appears to be encoded in LATIN-1 (or possibly 8859-13)
>
>
> I think you'll need to write a function.
>
> here, where I have database encoding UTF8 this appears to work as you
> desire.
>
> CREATE or replace FUNCTION htmlent(inp text) returns text as
> $f$
> DECLARE
>   str text;
> BEGIN
> str=3D regexp_replace(quote_literal( inp)
> ,$$&#(\d+);$$
> ,$$'||chr(\1)||'$$=20
> ,'g');
> execute 'select '||str into str;
> return str;
> END
> $f$
> LANGUAGE PLPGSQL;
>
> select htmlent('de patiënt niet');
>
> probably the above should be expanded to handle named entities=20
> like '&' too.
>
> characters outside of the LATIN-1 space are not handled=20
> but aparently this is what you want.
>
>
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
> --=__PartF6DE34E1.0__=
> Content-Type: text/html; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
>
>
>
>
>Thanks for the ideas!
>The function Jasen suggests works partially.
>There are also entities like
>‘
>
>’
>“
>”
>…
>The output of the htmlent function for these looks like | 
>Was that what you meant with "characters outside of the LATIN-1 space =
> are not handled
>but apparently this is what you want." ?
>Because in that case, they should be handled too.
>How should that be done.
>Thanks,
>Bart >>> Jasen Betts  gt; 2009-01-31 12:47 >>>On 2009-01-30, Bart Degryse  egr...@indicator.be> wrote:>> --=3D__Part8EA648F8.0__=3D R>> Content-Type: text/plain; charset=3DUTF-8> Content-Transfer-E=
> ncoding: quoted-printable>> Hi,> I have a text field =
> with data like this: 'de patiënt niet'> (without the =
> quotes).> I would like to convert this string to look like this: =
> 'de pati=C3=ABnt> niet'> Basically what I need to do (I =
> think) is> - get rid of the &, # and ;> - convert the =
> number to hex> - make a UTF8 from that (thus: \xEB)that is not =
> UTF8.the UTF8 representation for '=C3=AB' in  SQL is =
> e'\xC3\xAB' or chr(235)your input appears to be encoded in LATIN-1 =
> (or possibly 8859-13)I think you'll need to write a function. R>here, where I have database encoding UTF8 this appears to work as =
> youdesire.CREATE or replace FUNCTION htmlent(inp text) returns =
> text as$f$DECLARE  str text;BEGINstr=3D regexp_rep=
> lace(quote_literal( inp)=
> ;    =
> ,$$&#(\d+);$$    ,$$'||chr(\1)||'$$   =
>   ,'g');execute 'select '||str into str;return str;END=
> $f$LANGUAGE PLPGSQL;select htmlent('de patiënt =
> niet');probably the above should be expanded to handle named =
> entities like '&' too.characters outside of the =
> LATIN-1 space are not handled but aparently this is what you want.<=
> BR>-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)>To make changes to your subscription:http://www.postgresql.=
> org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql>
>
> --=__PartF6DE34E1.0__=--
>


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org

Re: [SQL] regexp_replace and UTF8

[Bart Degryse]

Thanks for the ideas!
The function Jasen suggests works partially.
There are also entities like
‘
’
“
”
…
The output of the htmlent function for these looks like | 
Was that what you meant with "characters outside of the LATIN-1 space
are not handled
but apparently this is what you want." ?
Because in that case, they should be handled too.
How should that be done.
Thanks,
Bart 


>>> Jasen Betts  2009-01-31 12:47 >>>
On 2009-01-30, Bart Degryse  wrote:
>
> --=__Part8EA648F8.0__=
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
> I have a text field with data like this: 'de patiënt niet'
> (without the quotes).
> I would like to convert this string to look like this: 'de patiënt
> niet'
> Basically what I need to do (I think) is
> - get rid of the &, # and ;
> - convert the number to hex
> - make a UTF8 from that (thus: \xEB)
that is not UTF8.

the UTF8 representation for 'ë' in  SQL is e'\xC3\xAB' or chr(235)

your input appears to be encoded in LATIN-1 (or possibly 8859-13)


I think you'll need to write a function.

here, where I have database encoding UTF8 this appears to work as you
desire.

CREATE or replace FUNCTION htmlent(inp text) returns text as
$f$
DECLARE
  str text;
BEGIN
str= regexp_replace(quote_literal( inp)
,$$&#(\d+);$$
,$$'||chr(\1)||'$$ 
,'g');
execute 'select '||str into str;
return str;
END
$f$
LANGUAGE PLPGSQL;

select htmlent('de patiënt niet');

probably the above should be expanded to handle named entities 
like '&' too.

characters outside of the LATIN-1 space are not handled 
but aparently this is what you want.


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

Re: [SQL] regexp_replace and UTF8

[Jasen Betts]

On 2009-01-30, Bart Degryse  wrote:
>
> --=__Part8EA648F8.0__=
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
> I have a text field with data like this: 'de patiënt niet'
> (without the quotes).
> I would like to convert this string to look like this: 'de patiënt
> niet'
> Basically what I need to do (I think) is
> - get rid of the &, # and ;
> - convert the number to hex
> - make a UTF8 from that (thus: \xEB)

that is not UTF8.

the UTF8 representation for 'ë' in  SQL is e'\xC3\xAB' or chr(235)

your input appears to be encoded in LATIN-1 (or possibly 8859-13)


I think you'll need to write a function.

here, where I have database encoding UTF8 this appears to work as you
desire.

CREATE or replace FUNCTION htmlent(inp text) returns text as
$f$
DECLARE
  str text;
BEGIN
 str= regexp_replace(quote_literal( inp)
,$$&#(\d+);$$
,$$'||chr(\1)||'$$ 
,'g');
 execute 'select '||str into str;
 return str;
END
$f$
LANGUAGE PLPGSQL;

select htmlent('de patiënt niet');

probably the above should be expanded to handle named entities 
like '&' too.

characters outside of the LATIN-1 space are not handled 
but aparently this is what you want.


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

Re: [SQL] regexp_replace and UTF8

[Harald Fuchs]

In article <87ljstm4eq@oxford.xeocode.com>,
Gregory Stark  writes:

> "Bart Degryse"  writes:
>> Hi,
>> I have a text field with data like this: 'de patiënt niet'

>> Can anyone help me fix this or point me to a better approach.
>> By the way, changing the way data is put into the field is
>> unfortunately not an option.

> You could use a plperl function to use one of the many html parsing perl
> modules?

Yes, either plperl or some external HTML tool.

>> Basically what I need to do (I think) is
>> - get rid of the &, # and ;
>> - convert the number to hex
>> - make a UTF8 from that (thus: \xEB)
>> - convert that to SQL_ASCII

You know that SQL_ASCII is a misnomer for "no encoding at all, and I
don't care"?  I'd use UTF8 or (if you stay in Western Europe) Latin9.


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

Re: [SQL] regexp_replace and UTF8

[Gregory Stark]

"Bart Degryse"  writes:

> Hi,
> I have a text field with data like this: 'de patiënt niet'

> Can anyone help me fix this or point me to a better approach.
> By the way, changing the way data is put into the field is
> unfortunately not an option.
> Many thanks in advance.

You could use a plperl function to use one of the many html parsing perl
modules?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

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

[SQL] regexp_replace and UTF8

[Bart Degryse]

Hi,
I have a text field with data like this: 'de patiënt niet'
(without the quotes).
I would like to convert this string to look like this: 'de patiënt
niet'
Basically what I need to do (I think) is
- get rid of the &, # and ;
- convert the number to hex
- make a UTF8 from that (thus: \xEB)
- convert that to SQL_ASCII
Since this works:
  select regexp_replace('de patiënt niet', '&#(\\d+);' (
file://d+);'/ ), '\xEB', 'g')
I was thinking that this would work too, but it doesn't
  select regexp_replace('de patiënt niet', '&#(\\d+);' (
file://\d+);' ), '\x' || to_hex(E'\\1'), 'g')
It gives me:
  ERROR:  function to_hex("unknown") is not unique
  HINT:  Could not choose a best candidate function. You may need to
add explicit type casts.
So I changed it to
  select regexp_replace('de patiënt niet', '&#(\\d+);' (
file://\d+);' ), '\x' || to_hex(CAST (to_number(E'\\1','999') AS
INTEGER)), 'g')
which kind of works except that the result is not what I need:
  'de patix1nt niet'
Can anyone help me fix this or point me to a better approach.
By the way, changing the way data is put into the field is
unfortunately not an option.
Many thanks in advance.
 
ICT Departement - Indicator NV
Bart Degryse

Re: [SQL] regexp_replace

[Marcin Krawczyk]

thanks / dzieki

regards / pozdrowienia
mk

2008/8/1 Pawel Socha <[EMAIL PROTECTED]>:
> 2008/8/1 Marcin Krawczyk <[EMAIL PROTECTED]>
>>
>> Hi all. I'd like to know whether it's possible to reverse the
>> behaviour of regexp_replace, meaning :
>> now if I do
>> SELECT regexp_replace ('foobarbaz', 'b..', 'X') I get 'fooXbaz' - it
>> replaces the string that matches given pattern with 'X', how do I
>> achieve the opposite - replace the string that doesn't match the
>> pattern ?
>>
>> regards
>> mk
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
> merlin=# SELECT regexp_replace ('foobarbaz', '[^b]', 'X', 'g');
>  regexp_replace
> 
>  XXXbXXbXX
> (1 row)
>
>
> --
> --
> Serdecznie pozdrawiam
>
> Pawel Socha
> [EMAIL PROTECTED]
>
> programista/administrator
>
> perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
> a%%s%%$_%ee'
>

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

Re: [SQL] regexp_replace

[Pawel Socha]

2008/8/1 Marcin Krawczyk <[EMAIL PROTECTED]>

> Hi all. I'd like to know whether it's possible to reverse the
> behaviour of regexp_replace, meaning :
> now if I do
> SELECT regexp_replace ('foobarbaz', 'b..', 'X') I get 'fooXbaz' - it
> replaces the string that matches given pattern with 'X', how do I
> achieve the opposite - replace the string that doesn't match the
> pattern ?
>
> regards
> mk
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



merlin=# SELECT regexp_replace ('foobarbaz', '[^b]', 'X', 'g');
 regexp_replace

 XXXbXXbXX
(1 row)


-- 
--
Serdecznie pozdrawiam

Pawel Socha
[EMAIL PROTECTED]

programista/administrator

perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
a%%s%%$_%ee'

[SQL] regexp_replace

[Marcin Krawczyk]

Hi all. I'd like to know whether it's possible to reverse the
behaviour of regexp_replace, meaning :
now if I do
SELECT regexp_replace ('foobarbaz', 'b..', 'X') I get 'fooXbaz' - it
replaces the string that matches given pattern with 'X', how do I
achieve the opposite - replace the string that doesn't match the
pattern ?

regards
mk

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

Re: [SQL] regexp_replace usage

[chester c young]

Thanks !Michael Fuhr <[EMAIL PROTECTED]> wrote:Offhand I can't think of a way to do what you want with regexp_replace()but you could use PL/Perl.  Something like this should work:CREATE FUNCTION mcfix(text) RETURNS text AS $$  $_[0] =~ s/\bMc([a-z])/Mc\u$1/g;  return $_[0];$$ LANGUAGE plperl IMMUTABLE STRICT;SELECT mcfix('John Mcneil');mcfix- John McNeil(1 row) 
		How low will we go? Check out Yahoo! Messenger’s low  PC-to-Phone call rates.

Re: [SQL] regexp_replace usage

[Michael Fuhr]

On Fri, Sep 29, 2006 at 02:31:12PM -0700, chester c young wrote:
> column name in table bue has miscapitalized Mc names, eg, 'John Mcneil'
> instead of 'John McNeil'.
> 
> (this should be easy but) how do you construct the update query?
> 
> also, regexp_string( 'Mcneil', 'Mc(.*)', initcap('\\1') ) => 'neil'
> _not_ Neil' - is this correct?

I assume you mean regexp_replace() as indicated in the subject.
You're calling initcap() on the literal value '\\1'; the result,
which is the same string, is then passed to regexp_replace(), so
in effect you're calling

regexp_replace('Mcneil', 'Mc(.*)', '\\1')

Offhand I can't think of a way to do what you want with regexp_replace()
but you could use PL/Perl.  Something like this should work:

CREATE FUNCTION mcfix(text) RETURNS text AS $$
  $_[0] =~ s/\bMc([a-z])/Mc\u$1/g;
  return $_[0];
$$ LANGUAGE plperl IMMUTABLE STRICT;

SELECT mcfix('John Mcneil');
mcfix
-
 John McNeil
(1 row)

You could use

   $_[0] =~ s/\b(Ma?c)([a-z])/$1\u$2/g;

to change Mcdonald and Macdonald into McDonald and MacDonald,
respectively.  However, since both Macdonald and MacDonald are used,
determining which is correct would be impossible.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] regexp_replace usage

[chester c young]

column name in table bue has miscapitalized Mc names, eg, 'John Mcneil' instead of 'John McNeil'.(this should be easy but) how do you construct the update query?also, regexp_string( 'Mcneil', 'Mc(.*)', initcap('\\1') ) => 'neil' _not_ Neil' - is this correct? 
		 All-new Yahoo! Mail - Fire up a more powerful email and get things done faster.