pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
escaping wildcard chars
4+ messages / 2 participants
[nested] [flat]

* escaping wildcard chars
@ 1999-07-26 05:16 Dan Wilson <dan_wilson@geocities.com>
  1999-07-26 12:39 ` Re: [GENERAL] escaping wildcard chars Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 1 reply; 4+ messages in thread

From: Dan Wilson @ 1999-07-26 05:16 UTC (permalink / raw)
  To: pgsql general <pgsql-general@postgresql.org>

I am trying to pull in the different types allowed by postgres for a create
function statement and am having problems with the types that start with an
underscore (_).  Because the underscore is the wildcard  for a single
character, I cannot perform the  following query correctly.

SELECT typname from pg_type WHERE NOT LIKE '_%'

It gives me an empty set.

Is there any way to escape the underscore.  I tried to use '\_%', but that
didn't help.  Any suggestions?

-Dan




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [GENERAL] escaping wildcard chars
  1999-07-26 05:16 escaping wildcard chars Dan Wilson <dan_wilson@geocities.com>
@ 1999-07-26 12:39 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1999-07-26 13:48   ` Re: [GENERAL] escaping wildcard chars Dan Wilson <dan_wilson@geocities.com>
  1999-07-26 13:52   ` Re: [GENERAL] escaping wildcard chars Dan Wilson <dan_wilson@geocities.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Herouth Maoz @ 1999-07-26 12:39 UTC (permalink / raw)
  To: Dan Wilson <dan_wilson@geocities.com>; pgsql general <pgsql-general@postgreSQL.org>; pgsql-sql

At 08:16 +0300 on 26/07/1999, Dan Wilson wrote:


> SELECT typname from pg_type WHERE NOT LIKE '_%'
>
> It gives me an empty set.
>
> Is there any way to escape the underscore.  I tried to use '\_%', but that
> didn't help.  Any suggestions?

Yes. Use '\\_%' instead. You see, a backslash is interpreted immediately as
"take the next char literally". This passes the underscore literally to
LIKE. Which is the same as '_%'. If you put a double backslash, the first
backslash takes the second one literally, thus passing '\_%' to LIKE. And
then LIKE knows that it should treat the underscore as non-special.

Ugly, ugly. I think we had a discussion either here or in one of the other
lists regarding the ESCAPE clause to LIKE. This behavior means that even if
we write ESCAPE '\', it won't work (Actually, it should be '\\'.

I really hate those backslashes. They are blatantly incompatible with SQL92
and will cause standard SQL to fail on Postgres, (and of course, Postgres
code to fail on other databases). There should be a setting, either in the
backend or in a SET command, such as "BACKSLASH_BEHAVIOR", which will be
either "literal" or "escape". It can default to the current behavior
(namely "escape") so that current code won't fail, but will enable people
to write sane standard code.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [GENERAL] escaping wildcard chars
  1999-07-26 05:16 escaping wildcard chars Dan Wilson <dan_wilson@geocities.com>
  1999-07-26 12:39 ` Re: [GENERAL] escaping wildcard chars Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-07-26 13:48   ` Dan Wilson <dan_wilson@geocities.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Dan Wilson @ 1999-07-26 13:48 UTC (permalink / raw)
  To: pgsql general <pgsql-general@postgreSQL.org>; pgsql-sql

> I really hate those backslashes. They are blatantly incompatible with
SQL92
> and will cause standard SQL to fail on Postgres, (and of course, Postgres
> code to fail on other databases). There should be a setting, either in the
> backend or in a SET command, such as "BACKSLASH_BEHAVIOR", which will be
> either "literal" or "escape". It can default to the current behavior
> (namely "escape") so that current code won't fail, but will enable people
> to write sane standard code.

Thanks for the info and I totally agree.  There should be some type of way
to turn it off or on.

-Dan




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [GENERAL] escaping wildcard chars
  1999-07-26 05:16 escaping wildcard chars Dan Wilson <dan_wilson@geocities.com>
  1999-07-26 12:39 ` Re: [GENERAL] escaping wildcard chars Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-07-26 13:52   ` Dan Wilson <dan_wilson@geocities.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Dan Wilson @ 1999-07-26 13:52 UTC (permalink / raw)
  To: pgsql general <pgsql-general@postgreSQL.org>; pgsql-sql

> Yes. Use '\\_%' instead. You see, a backslash is interpreted immediately
as
> "take the next char literally". This passes the underscore literally to
> LIKE. Which is the same as '_%'. If you put a double backslash, the first
> backslash takes the second one literally, thus passing '\_%' to LIKE. And
> then LIKE knows that it should treat the underscore as non-special.

I tried this and it didn't work either.  I eventually got carried away and
did '\\\_%' and it worked.  What a pain!

> I really hate those backslashes. They are blatantly incompatible with
SQL92
> and will cause standard SQL to fail on Postgres, (and of course, Postgres
> code to fail on other databases). There should be a setting, either in the
> backend or in a SET command, such as "BACKSLASH_BEHAVIOR", which will be
> either "literal" or "escape". It can default to the current behavior
> (namely "escape") so that current code won't fail, but will enable people
> to write sane standard code.


I totally agree here.  There should be some way to turn it off so that those
who are starting out can write standard stuff.

-Dan




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-07-26 13:52 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-26 05:16 escaping wildcard chars Dan Wilson <dan_wilson@geocities.com>
1999-07-26 12:39 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-07-26 13:48   ` Dan Wilson <dan_wilson@geocities.com>
1999-07-26 13:52   ` Dan Wilson <dan_wilson@geocities.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox