pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Search for underscore w/ LIKE
5+ messages / 4 participants
[nested] [flat]

* Search for underscore w/ LIKE
@ 2000-07-07 09:09 brianb-pgsql@edsamail.com
  2000-07-07 17:33 ` Re: Search for underscore w/ LIKE Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: brianb-pgsql@edsamail.com @ 2000-07-07 09:09 UTC (permalink / raw)
  To: pgsql-sql


How do I use LIKE to search for strings with an underscore? The
documentation (well, Bruce's book) says to use 2 underscores (__) but it
doesn't work. For example:

create table liketest (
    somestr varchar(50)
);

insert into liketest values ('foo_bar');
insert into liketest values ('foobar');
insert into liketest values ('snackbar');
insert into liketest values ('crow_bar');

-- I want to select strings with "_bar"
select * from liketest where somestr like '%_bar';
 somestr  
----------
 foo_bar
 foobar
 snackbar
 crow_bar
(4 rows)

-- Using double underscore doesn't work either
select * from liketest where somestr like '%__bar';
 somestr  
----------
 foo_bar
 foobar
 snackbar
 crow_bar
(4 rows)

-- Escaping w/ backslash doesn't work 
select * from liketest where somestr like '%\_bar';
 somestr  
----------
 foo_bar
 foobar
 snackbar
 crow_bar
(4 rows)

Brian
--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/ AIM: bbaquiran 
Work: (632)7182222       Home: (632)9227123



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

* Re: Search for underscore w/ LIKE
  2000-07-07 09:09 Search for underscore w/ LIKE brianb-pgsql@edsamail.com
@ 2000-07-07 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-07-07 18:21   ` Re: Search for underscore w/ LIKE Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2000-07-07 17:33 UTC (permalink / raw)
  To: brianb-pgsql@edsamail.com; +Cc: pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>

brianb-pgsql@edsamail.com writes:
> How do I use LIKE to search for strings with an underscore? The
> documentation (well, Bruce's book) says to use 2 underscores (__) but it
> doesn't work.

If Bruce's book says that, I hope it's not too late for him to change
it ;-)

The correct way is to escape the underscore with a backslash.  You
actually have to write two backslashes in your query:

	select * from foo where bar like '%\\_baz'

The first backslash quotes the second one for the query parser, so that
what ends up inside the system is %\_baz, and then the LIKE function
knows what to do with that.

Similarly, '\\%' would be the way to match a literal %.  You can
actually backslash-quote any single character this way in LIKE,
but % and _ are the only ones where it makes a difference.  (In the
regexp-matching operators there are many more special characters and
so many more times when you need the backslash trick.)

			regards, tom lane



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

* Re: Search for underscore w/ LIKE
  2000-07-07 09:09 Search for underscore w/ LIKE brianb-pgsql@edsamail.com
  2000-07-07 17:33 ` Re: Search for underscore w/ LIKE Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-07-07 18:21   ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-07-07 18:24     ` Re: Search for underscore w/ LIKE Bruce Momjian <pgman@candle.pha.pa.us>
  2000-07-08 00:09     ` Re: Search for underscore w/ LIKE Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 2 replies; 5+ messages in thread

From: Tom Lane @ 2000-07-07 18:21 UTC (permalink / raw)
  To: brianb-pgsql@edsamail.com, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>

I wrote:
> Similarly, '\\%' would be the way to match a literal %.  You can
> actually backslash-quote any single character this way in LIKE,
> but % and _ are the only ones where it makes a difference.

Er, check that.  Backslash itself also needs quoting in LIKE.

Exercise for the student: if you need to match a literal backslash
in a LIKE pattern, how many backslashes do you have to write in your
query?

			regards, tom lane



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

* Re: Search for underscore w/ LIKE
  2000-07-07 09:09 Search for underscore w/ LIKE brianb-pgsql@edsamail.com
  2000-07-07 17:33 ` Re: Search for underscore w/ LIKE Tom Lane <tgl@sss.pgh.pa.us>
  2000-07-07 18:21   ` Re: Search for underscore w/ LIKE Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-07-07 18:24     ` Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Bruce Momjian @ 2000-07-07 18:24 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: brianb-pgsql@edsamail.com; pgsql-sql

> I wrote:
> > Similarly, '\\%' would be the way to match a literal %.  You can
> > actually backslash-quote any single character this way in LIKE,
> > but % and _ are the only ones where it makes a difference.
> 
> Er, check that.  Backslash itself also needs quoting in LIKE.
> 
> Exercise for the student: if you need to match a literal backslash
> in a LIKE pattern, how many backslashes do you have to write in your
> query?

I will fix the book.  Seems I didn't do the test properly.


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



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

* Re: Search for underscore w/ LIKE
  2000-07-07 09:09 Search for underscore w/ LIKE brianb-pgsql@edsamail.com
  2000-07-07 17:33 ` Re: Search for underscore w/ LIKE Tom Lane <tgl@sss.pgh.pa.us>
  2000-07-07 18:21   ` Re: Search for underscore w/ LIKE Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-07-08 00:09     ` Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 0 replies; 5+ messages in thread

From: Peter Eisentraut @ 2000-07-08 00:09 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: brianb-pgsql@edsamail.com, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>

Tom Lane writes:

> Exercise for the student: if you need to match a literal backslash
> in a LIKE pattern, how many backslashes do you have to write in your
> query?

I like how Python handles this: You prefix the text literal with an `r'
(as in "raw") and the backslashes are not special. Maybe we could offer
that as well.

	blah ~ r'.+\..+'

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




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


end of thread, other threads:[~2000-07-08 00:09 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-07 09:09 Search for underscore w/ LIKE brianb-pgsql@edsamail.com
2000-07-07 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-07 18:21   ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-07 18:24     ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-07-08 00:09     ` Peter Eisentraut <peter_e@gmx.net>

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