[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_%' ID NAME ---------- ------------------------------ 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 '\' ID NAME ---------- ------------------------------ 2 xy_abc_yz