Обсуждение: Regular expressions or LIKE ? HELP needed !

Hi,

I have a little trouble ... my level with regular expression is not at
the top ... but I found two troubles and may I do not use them well ...
may be it's another things not detailed in any documentation of
PostgreSQL ...

Case 1 :
select name block where code ~* 'lower(';
ERROR:  Invalid regular expression: parentheses ( ) not balanced

select name block where code ~* 'lower\(';
ERROR:  Invalid regular expression: parentheses ( ) not balanced

How to escape a "(" ??

Case 2 :
select name from block where code ilike '%lower(%';
This is running ... ;)

But I want to find a text inside my field code having : lower(' inside
with the quote ("'") code at the end ... ok ?

So I thought I have to double the quote like this ... I have no result
... but I know result exists !
select name from block where code ike '%lower(''%';

I have also tried :
select name from block where code ike '%lower(\'%';
Same thing ... no result ... but I'm sure I have some !

Any idea how to escape the quote caracter in a LIKE ?

Many thanks for you help !

Regards,
--
Hervé Piedvache

Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honoré
F-75008 - Paris - France
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902
Email: herve@elma.fr

> How to escape a "(" ??

select name block where code ~* 'lower\\(';

First, you have to escape the '(' in order to make regexps match the bracket
instead of using it as a grouping operator.

And then you have to escape the escape in order to get it passed to the regexp
system (at least with psql).

> select name from block where code ike '%lower(''%';

Works for me in 7.2.1 (ike should be ilike). Which postgres version are you
running?

With kind regards / mit freundlichem Gruß
    Holger Klawitter
--
Holger Klawitter                          http://www.klawitter.de
lists@klawitter.de

A safe method that always works is simply to use a
character class, like:

select name block where code ~* 'lower [(]'

--- Holger Klawitter <lists@klawitter.de> wrote:
>
> > How to escape a "(" ??
>
> select name block where code ~* 'lower\\(';
>
> First, you have to escape the '(' in order to make
> regexps match the bracket
> instead of using it as a grouping operator.
>
> And then you have to escape the escape in order to
> get it passed to the regexp
> system (at least with psql).
>
> > select name from block where code ike
> '%lower(''%';
>
> Works for me in 7.2.1 (ike should be ilike). Which
> postgres version are you
> running?
>
> With kind regards / mit freundlichem Gru�
>     Holger Klawitter
> --
> Holger Klawitter
> http://www.klawitter.de
> lists@klawitter.de
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com

> A safe method that always works is simply to use a
> character class, like:
>
> select name block where code ~* 'lower [(]'

But be warned when trying to match a closing Bracket :-)

With kind regards / mit freundlichem Gruß
    Holger Klawitter
--
Holger Klawitter                          http://www.klawitter.de
lists@klawitter.de

Hi Holger,

Holger Klawitter a écrit :
>
> > How to escape a "(" ??
>
> select name block where code ~* 'lower\\(';
>
> First, you have to escape the '(' in order to make regexps match the bracket
> instead of using it as a grouping operator.
>
> And then you have to escape the escape in order to get it passed to the regexp
> system (at least with psql).

OK thanks !

> > select name from block where code ike '%lower(''%';
>
> Works for me in 7.2.1 (ike should be ilike). Which postgres version are you
> running?

ilike yes loose something in copy/paste ;)

I'm using 7.2.1 ... and it's running ... I find the mistake ... the data
are escaped in the database so I have to do :

select name from block where code ilike '%lower(\\\\''%';

regards,
--
Hervé Piedvache

Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honoré
F-75008 - Paris - France
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902
Email: herve@elma.fr