The REGEXP_LIKE pattern matching condition is similar to the LIKE condition. Whereas LIKE only performs simple pattern matching using the wildcards "%" and "_", REGEXP_LIKE performs complex regular expression pattern matching allowing it to match a much greater range of string patterns than LIKE. The following examples compare pattern matching with both LIKE and REGEXP_LIKE.
Before proceeding however, the following code will set up some test data for us (see Setup if you want to create the SET_TARGET and ADD_TARGET procedures on your own test system).
execute set_target( 'aaa' ) execute add_target( 'abc' ) execute add_target( 'bbb' ) execute add_target( 'bcd' ) execute add_target( 'cde' ) execute add_target( 'def' ) execute add_target( 'efg' ) column target heading "TARGET|STRING"
LIKE vs REGEXP_LIKE
| Matching an entire string is performed as follows. | |
select target from targets where target LIKE 'abc' ; TARGET STRING ---------- abc |
select target from targets where REGEXP_LIKE( target, '^abc$' ) ; TARGET STRING ---------- abc |
| This is how to match a string anywhere in the target. | |
select target from targets where target LIKE '%bc%' ; TARGET STRING ---------- abc bcd |
select target from targets where REGEXP_LIKE( target, 'bc' ) ; TARGET STRING ---------- abc bcd |
| These examples show how to match a string at the start of the target. | |
select target from targets where target LIKE 'b%' ; TARGET STRING ---------- bbb bcd |
select target from targets where REGEXP_LIKE( target, '^b' ) ; TARGET STRING ---------- bbb bcd |
| These examples show how to match a string at the end of the target. | |
select target from targets where target LIKE '%b' ; TARGET STRING ---------- bbb |
select target from targets where REGEXP_LIKE( target, 'b$' ) ; TARGET STRING ---------- bbb |
| To match any single character do the following. | |
select target from targets where target LIKE 'a_c' ; TARGET STRING ---------- abc |
select target from targets where REGEXP_LIKE( target, '^a.c$' ) ; TARGET STRING ---------- abc |
| One example of a complex pattern that REGEXP_LIKE can handle but a single, basic LIKE condition cannot (without using text manipulation functions) is a list of characters. In this example we search for strings that have either an "a", "c", or "f" as the second character of the string. | |
-- -- not possible -- |
select target from targets where REGEXP_LIKE( target, '^.[acf]' ) ; TARGET STRING ---------- aaa bcd efg |
To learn more about regular expression patterns in general see Pattern Matching.