Thread: Search for underscore w/ LIKE

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_barfoobarsnackbarcrow_bar
(4 rows)

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

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

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

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

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

> 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,
Pennsylvania19026
 

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