The LIKE operator is a very useful one. It is used to match strings with partial match while using the underscore (‘_’) as a single character wildcard and the percentage sign (‘%’) as multiple character wildcard.
There are a few things you should know and some tricks you can use with the LIKE operator:
- The underscore (‘_’) is a wildcard for exactly one character, it won’t work with no character at all. Example: if we use “x like ‘A_'” in our query, when x is ‘AA’ it will return true, but when x is only ‘A’ it will return false.
- The percentage sign (‘%’) is a wildcard for zero or more characters. With the same example from above, if we use “x like ‘A%'”, both x=’AA’ and x=’A’ will return true.
- Using wildcards anywhere but the beginning of the search string can use an index (e.g. like ‘AA_’, like ‘AB%C’). A wildcard at the beginning will not allow Oracle to use an index (e.g. like ‘%AA’).
- In order to search the characters underscore or percentage we can use an escape character. For example to search something beginning with ‘AA%’ we should use: “where x like ‘AA\%’ escape ‘\'”. Otherwise we will get everything starting with ‘AA’.
- We can concatenate wildcards. For example, if we would like to get all rows where the column pref is a prefix of the column full (for example, where prefix=’AA’ and full=’AABBCC’) we can use: “where full like pref||’%'”.
