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.


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