pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSearch for underscore w/ LIKE
5+ messages / 4 participants
[nested] [flat]
* Search for underscore w/ LIKE
@ 2000-07-07 09:09 brianb-pgsql@edsamail.com
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 17:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: brianb-pgsql@edsamail.com
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 18:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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 18:24 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.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-08 00:09 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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