LIKE Operator

The LIKE operator is unique, in that it’s used with wildcard characters to search for patterns. Wildcard characters are used to represent one or more alphanumeric characters. The wildcard characters available for pattern searches in Oracle 11g are the percent sign (%) and the underscore symbol ( _ ). The percent sign represents any number of characters (zero, one, or more), and the underscore symbol represents exactly one character

find any customer whose last name starts with P and don’t care about the remaining letters of the last name

SELECT lastname
FROM customers
WHERE lastname LIKE 'P%'

Look for any customer number that begins with 10, is followed by any character, and ends with 9.”

SELECT *
FROM customers
WHERE customer# LIKE '10_9';

What if you need to use the LIKE operator to search for patterns but also need to search for a wildcard character as a literal in your value? For example, you need to search for a value that starts with the % symbol, contains an uppercase A as the fourth character, and ends with an uppercase T. In this query, you need to use the wildcard characters _ and % with the LIKE operator but also need to search for a literal % symbol as the first character. The LIKE operator includes the ESCAPE option for indicating when wildcard symbols should be used as literals rather than translated as wildcard characters. This option allows the user to select the escape character. The escape character must precede any wildcard characters in the search pattern that should be interpreted literally, not as wildcard characters

SELECT * 
FROM testing
 WHERE tvalue LIKE '\%_A%T' ESCAPE '\';

Back To Top- Home - Category