Home > mailing lists

Thread: Odd behavior with LIKE?

Odd behavior with LIKE?

From

"Tim Barnard"

Date:

11 June 2001, 17:52:25

I've noticed that if I don't preceed an underscore character ( _ )

with a double backslash ( \\ ), then a select using LIKE

ignores the underscore. For example, I have a couple of indexes

that end with "_ts" and a few tables that end in "ts":

From within psql:

select relname from pg_class;

pg_type

pg_attribute

raw_ts

accts

adjacents

areacodes

and so on.

Now if I change the select to omit all tables/indexes

that begin with "pg_" or end with "_pkey":

select relname from pg_class where relname not like 'pg_%' and relname not like '%_pkey';

the "pg_" and "_pkey" files are correctly ommitted.

However, if I also want to omit all tables/indexes

ending with "_ts":

select relname from pg_class where relname not like 'pg_%' and relname not like '%_pkey' and relname not like '_ts';

then not only is "raw_ts" omitted (which is what I want),

but so are the tables "accts" and "adjacents". I can

correct this with:

select relname from pg_class where relname not like 'pg_%' and relname not like '%_pkey' and relname not like '\\_ts';

and probably whould do so for all conditions:

select relname from pg_class where relname not like 'pg\\_%' and relname not like '%\\_pkey' and relname not like '\\_ts';

Question is: Why must the underscore character

be prefixed with a double-backslash?

Tim

Re: Odd behavior with LIKE?

From

Date:

11 June 2001, 18:23:20

On Mon, Jun 11, 2001 at 11:00:36AM -0700, some SMTP stream spewed forth:
> I've noticed that if I don't preceed an underscore character ( _ )
> with a double backslash ( \\ ), then a select using LIKE
> ignores the underscore. For example, I have a couple of indexes
> that end with "_ts" and a few tables that end in "ts":

Quote /usrs-lounge/docs/7.1/user/functions-matching.html#FUNCTIONS-LIKE
...
An underscore (_) in pattern stands for (matches) any single character; a
percent sign (%) matches any string of zero or more characters.
...
'abc' LIKE '_b_'    true

> select relname from pg_class where relname not like 'pg\\_%' and relname not like '%\\_pkey' and relname not like
'\\_ts';
>
> Question is: Why must the underscore character
> be prefixed with a double-backslash?

It must be escaped because it a special pattern-matching character.


gh

> Tim
>

Re: Odd behavior with LIKE?

From

"Tim Barnard"

Date:

11 June 2001, 18:43:19

Thanks. Somehow I missed that :-(

Tim

----- Original Message -----
From: "GH" <grasshacker@over-yonder.net>
To: "Tim Barnard" <tbarnard@povn.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, June 11, 2001 12:23 PM
Subject: Re: [GENERAL] Odd behavior with LIKE?


> On Mon, Jun 11, 2001 at 11:00:36AM -0700, some SMTP stream spewed forth:
> > I've noticed that if I don't preceed an underscore character ( _ )
> > with a double backslash ( \\ ), then a select using LIKE
> > ignores the underscore. For example, I have a couple of indexes
> > that end with "_ts" and a few tables that end in "ts":
>
> Quote /usrs-lounge/docs/7.1/user/functions-matching.html#FUNCTIONS-LIKE
> ...
> An underscore (_) in pattern stands for (matches) any single character; a
> percent sign (%) matches any string of zero or more characters.
> ...
> 'abc' LIKE '_b_'    true
>
> > select relname from pg_class where relname not like 'pg\\_%' and relname
not like '%\\_pkey' and relname not like '\\_ts';
> >
> > Question is: Why must the underscore character
> > be prefixed with a double-backslash?
>
> It must be escaped because it a special pattern-matching character.
>
>
> gh
>
> > Tim
> >
>

Re: Odd behavior with LIKE?

From

"Tim Barnard"

Date:

11 June 2001, 20:46:57

Sometimes you just miss things. That's the problem with not being perfect
:-)

Tim

----- Original Message -----
From: "Joseph Shraibman" <jks@selectacast.net>
To: "Tim Barnard" <tbarnard@povn.com>
Sent: Monday, June 11, 2001 2:45 PM
Subject: Re: [GENERAL] Odd behavior with LIKE?


> How could you miss that?  Those are the things that make LIKE different
> from =
>
> Tim Barnard wrote:
> >
> > Thanks. Somehow I missed that :-(
> >
> > Tim
> >
> > ----- Original Message -----
> > From: "GH" <grasshacker@over-yonder.net>
> > To: "Tim Barnard" <tbarnard@povn.com>
> > Cc: <pgsql-general@postgresql.org>
> > Sent: Monday, June 11, 2001 12:23 PM
> > Subject: Re: [GENERAL] Odd behavior with LIKE?
> >
> > > On Mon, Jun 11, 2001 at 11:00:36AM -0700, some SMTP stream spewed
forth:
> > > > I've noticed that if I don't preceed an underscore character ( _ )
> > > > with a double backslash ( \\ ), then a select using LIKE
> > > > ignores the underscore. For example, I have a couple of indexes
> > > > that end with "_ts" and a few tables that end in "ts":
> > >
> > > Quote
/usrs-lounge/docs/7.1/user/functions-matching.html#FUNCTIONS-LIKE
> > > ...
> > > An underscore (_) in pattern stands for (matches) any single
character; a
> > > percent sign (%) matches any string of zero or more characters.
> > > ...
> > > 'abc' LIKE '_b_'    true
> > >
> > > > select relname from pg_class where relname not like 'pg\\_%' and
relname
> > not like '%\\_pkey' and relname not like '\\_ts';
> > > >
> > > > Question is: Why must the underscore character
> > > > be prefixed with a double-backslash?
> > >
> > > It must be escaped because it a special pattern-matching character.
> > >
> > >
> > > gh
> > >
> > > > Tim
> > > >
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> --
> Joseph Shraibman
> jks@selectacast.net
> Increase signal to noise ratio.  http://www.targabot.com
>