Oracle query String including hyphen character

Question

I have query that I run on Oracle that is supposed to allow hyphen characters. The results are supposed to match the exact String including the hyphen character as follows:

SELECT <field> 
  FROM <table> 
 WHERE LOWER(field) LIKE '%-pa%';

The results however show "web-page", "web -page" as well as "web page". However, I only would like to find "web-page" and "web -page" in this case. I tried to escape the hyphen character with a backslash but that results in no records found. Can anybody give me a hint on how to make this work?

Answer 1

That's not my observation of how Oracle treats hyphens. Here's a brief sample of what I see:

SQL> select * from fb;

ID
----------
Web-Page
Web Page
Web -Page

SQL> select * from fb where lower(id) like '%-pa%';

ID
----------
Web-Page
Web -Page

Are you sure you're not using the underscore instead of the hyphen? The underscore is a single character wild card.

Answer 2

Normaly the hyphen shouldn't need to be escaped, but you can try

select <field> from <table> where lower(field) like '%X-pa%' escape 'X';

instead of 'X', you can use any arbitrary character