Did You Know #19 – Using LIKE

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||’%'”.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s