Difference between revisions of "LIKE Operator"

From rbachwiki
Jump to navigation Jump to search
(Created page with "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...")
 
Line 10: Line 10:
  WHERE lastname LIKE 'P%'
  WHERE lastname LIKE 'P%'


find missing number  
Look for any customer number that begins with 10, is followed by any character, and ends with 9.”
  SELECT *
  SELECT *
  FROM customers
  FROM customers
  WHERE customer# LIKE '10_9';
  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 '\';
[[#Rules for Dates|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]
[[#Rules for Dates|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]

Revision as of 18:28, 24 October 2017

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