René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

like [Oracle SQL]

'some-string' like 'some%other_string'
'some-string' like 'some%other_string' escape 'c'

'some-string' not like 'some%other_string'
'some-string' not like 'some%other_string' escape 'c'
c being a single character that can be used to escape % and _.

escape

The underscore matches any character. But if it is needed to actually match an underscore in a like query, the underscore must be escaped:
create table like_escape_test (
  some_text varchar2(20)
);

insert into like_escape_test values ('foo_bar'    );
insert into like_escape_test values ('foo bar'    );
insert into like_escape_test values ('baz_qqq'    );
insert into like_escape_test values ('10% or more');
insert into like_escape_test values ('10 percent' );
The following, wrong, statement tries to find all values that contain an underscore:
select * from like_escape_test where some_text like '%_%';
SOME_TEXT
--------------------
foo_bar
foo bar
baz_qqq
10% or more
Here's the corrected version:
select * from like_escape_test where some_text like '%\_%' escape '\';
SOME_TEXT
--------------------
foo_bar
baz_qqq
Likewise, the percent symbol can be escaped:
select * from like_escape_test where some_text like '%\%%' escape '\';
SOME_TEXT
--------------------
10% or more

Misc

poor man's text index is an example that uses collections to search in the middle of words in a table. (ie where column_name like '%word%')
Like searches are quite unsatisfying, at least in my humble opinion. Therefore, I have written a function that searches fuzzily: fuzzy search.
In 10g, there is a regular expression version of like: regexp_like.
like is a pattern condition.