Oracle – SELECT LIKE with ESCAPE clause

[adsense id=”0514458240″ width=”468″ height=”60″]

Oracle by wildcard values ‘%’ and also ‘_’. The ‘%’ allows none, one or more matching characters. For instance, let us imagine we have a SELECT statement filtering on some field containing the string ‘abc’. This can be done using:

SELECT * FROM table WHERE field LIKE '%abc%';

This implies that abc, aabc, aabca will all be picked up. So the ‘%’ implies none, one or more entries following a string.

Now let us look at the underscore ‘_’. This is a wildcard matching a single character. So for instance if we have:

SELECT * FROM table WHERE field LIKE '_abc_';

This implies that from the previous values (abc, aabc, aabca) only aabca matches correctly.

But what happens if we want to search for a single underscore. In other words we want to make sure that the ‘_’ is to be treated as an underscore and not as a wildcard? This can be resolved in Oracle using the ESCAPE clause.

SQL> r
  1* select * from names where name like '%_abc_%'

---------- ------------------------------
	 1 xyzabcxyz
	 2 xy_abc_yz

Both were picked up using the LIKE clause. Now we want to escape the underscore to indicate that this is a valid character in the string we are searching for.

  1* SELECT * FROM names WHERE name LIKE '%\_abc\_%' ESCAPE '\'

---------- ------------------------------
	 2 xy_abc_yz