Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
11g Release 2 (11.2.2)

E21642-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 Search Conditions

A search condition specifies criteria for choosing rows to select, update, or delete. Search conditions are parameters that can exist in clauses and expressions of any DML statements, such as INSERT...SELECT, UPDATE and CREATE VIEW and some DDL statements, such as CREATE VIEW.


Search condition general syntax

A search condition is a single predicate or several predicates connected by the logical operators AND or OR. A predicate is an operation on expressions that evaluates to TRUE, FALSE, or UNKNOWN. If a predicate evaluates to TRUE for a row, the row qualifies for further processing. If the predicate evaluates to FALSE or NULL for a row, the row is not available for operations.

SQL syntax

[NOT] 
{BetweenPredicate | ComparisonPredicate | InPredicate |
    LikePredicate | NullPredicate | InfinitePredicate | NaNPredicate |
QuantifiedPredicate |(SearchCondition)} 
[{AND | OR} [NOT] 
{BetweenPredicate | ComparisonPredicate | InPredicate |
    LikePredicate | NullPredicate | QuantifiedPredicate | (SearchCondition)}
] [...]

Parameters

Component Description
NOT, AND, OR Logical operators with the following functions:
  • NOT negates the value of the predicate that follows it.

  • AND evaluates to TRUE if both the predicates it joins evaluate to TRUE.

  • OR evaluates to TRUE if either predicate it joins evaluates to TRUE, and to FALSE if both predicates evaluates to FALSE.

  • See "Description" for a description of how these operators work when predicates evaluate to NULL.

BetweenPredicate Determines whether an expression is within a certain range of values. For example: A BETWEEN B AND C is equivalent to A >= B AND A<= C.
ComparisonPredicate Compares two expressions or list of two expressions using one of the operators <, <=, >, >=, =, <>.
InPredicate Determines whether an expression or list of expressions matches an element within a specified set.
ExistsPredicate Determines whether a subquery returns any row.
LikePredicate Determines whether an expression contains a particular character string pattern.
NullPredicate Determines whether a value is NULL.
InfinitePredicate Determines whether an expression is infinite (positive or negative infinity).
NaNPredicate Determines whether an expression is the undefined result of an operation ("not a number").
QuantifiedPredicate Determines whether an expression or list of expressions bears a particular relationship to a specified set.
(SearchCondition) One of the above predicates, enclosed in parentheses.

Description

Figure 5-1 Values that result from logical operations

Description of Figure 5-1 follows
Description of "Figure 5-1 Values that result from logical operations"


ALL / NOT IN predicate (subquery)

The ALL or NOT IN predicate indicates that the operands on the left side of the comparison must compare in the same way with all of the values that the subquery returns. The ALL predicate evaluates to TRUE if the expression or list of expressions relates to all rows returned by the subquery as specified by the comparison operator. Similarly, the NOT IN predicate evaluates to TRUE if the expression or list of expressions does not equal the value returned by the subquery.

SQL syntax

RowValueConstructor {CompOp ALL | NOT IN} (Subquery)

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstuctorList) | Subquery

The syntax for RowValueConstructorList:

RowValueConstructorElement [{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

Parameters

Component Description
Expression The syntax of expressions is defined under "Expression specification". Both numeric and non-numeric expressions are allowed for ALL predicates, but both expression types must be compatible with each other.
= Is equal to.
<> Is not equal to.
> Is greater than.
>= Is greater than or equal to.
< Is less than.
<= Is less than or equal to.
Subquery The syntax of subqueries is defined under "Subqueries"

Description

Examples

Examples of NOT IN with subqueries:

SELECT * FROM customers 
WHERE cid NOT IN
(SELECT cust_id FROM returns)
AND cid > 5000;

SELECT * FROM customers 
WHERE cid NOT IN
(SELECT cust_id FROM returns)
AND cid NOT IN
(SELECT cust_id FROM complaints);

SELECT COUNT(*) From customers 
WHERE cid NOT IN
(SELECT cust_id FROM returns)
AND cid NOT IN
(SELECT cust_id FROM complaints);

Select all books that are not from exclBookList or if the price of the book is higher than $20.

SELECT * FROM books 
 WHERE id NOT IN (SELECT id FROM exclBookList) OR books.price>20;

The following query returns the employee_id and job_id from the job_history table. It illustrates use of expression list and subquery with the NOT IN predicate.

Command> SELECT employee_id, job_id FROM job_history 
       > WHERE (employee_id, job_id)
       > NOT IN (SELECT employee_id, job_id FROM employees);
< 101, AC_ACCOUNT >
< 101, AC_MGR >
< 102, IT_PROG >
< 114, ST_CLERK >
< 122, ST_CLERK >
< 176, SA_MAN >
< 200, AC_ACCOUNT >
< 201, MK_REP >
8 rows found.

ALL / NOT IN predicate (value list)

The ALL / NOT IN quantified predicate compares an expression or list of expressions with a list of specified values. The ALL predicate evaluates to TRUE if all the values in the ValueList relate to the expression or list of expressions as indicated by the comparison operator. Similarly, the NOT IN predicate evaluates to TRUE if the expression or list of expressions does not equal one of the values in the list.

SQL syntax

RowValueConstructor {CompOp ALL | NOT IN} ValueList

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstuctorList) | 

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

The syntax for one element in the ValueList (no parentheses necessary):

ConstantExpression

The syntax for more than one element in the ValueList:

({ConstantExpression} [,...] )

The syntax for an empty ValueList:

( )

Parameters

Component Description
Expression Specifies a value to be obtained. The values in ValueList must be compatible with the expression. For information on the syntax of expressions, see "Expression specification".
ConstantExpression Specifies a constant value or an expression that evaluates to a constant value (such as a number, character string, or date). This includes support for bound values (? or :DynamicParameter), NULL, and calls to functions that return constant values.
= Is equal to.
<> Is not equal to.
> Is greater than.
>= Is greater than or equal to.
< Is less than.
<= Is less than or equal to.
ALL The predicate is TRUE if all the values in the ValueList relate to the expression or list of expressions as indicated by the comparison operator.
ValueList A list of values that are compared against the RowValueConstructor values.

The ValueList can be an empty list (sometimes generated by SQL generation tools) or consists of ConstantExpression entries.

The ValueList cannot include column references, sequences, subqueries, ROWID values, or ROWNUM values.

The ValueList can be nested if RowValueConstructor is a list. For example: (x, y) not in ((1+1, 2), (abs(-1), 5+1))


Description

Examples

To query an empty select list for a NOT IN condition:

SELECT * FROM t1 WHERE x1 NOT IN ();

For ValueList examples, see the Examples section in "ANY / IN predicate (value list)".


ANY / IN predicate (subquery)

An ANY predicate compares two expressions using a comparison operator. The predicate evaluates to TRUE if the first expression relates to anyrow returned by the subquery as specified by the comparison operator. Similarly, the IN predicate compares an expression or list of expressions with a table subquery. The IN predicate evaluates to TRUE if the expression or list of expressions is equal to a value returned by a subquery.

SQL syntax

RowValueConstructor {CompOp ANY | IN} (Subquery)

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstuctorList) | Subquery

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

Parameters

Component Description
Expression The syntax of expressions is defined under "Expression specification". Both numeric and non-numeric expressions are allowed for ANY predicates, but both expression types must be compatible with each other.
= Is equal to.
<> Is not equal to.
> Is greater than.
>= Is greater than or equal to.
< Is less than.
<= Is less than or equal to.
Subquery The syntax of subqueries is defined under "Subqueries".

Description

The ANY predicate, which returns zero or more rows, uses a comparison operator modified with the keyword ANY. See "Numeric data types" for information about how TimesTen compares values of different but compatible types.

Examples

This example retrieves a list of customers having at least one unshipped order:

SELECT customers.name FROM customers 
WHERE customers.id = ANY 
(SELECT orders.custid FROM orders 
WHERE orders.status = 'unshipped');

This is an example of an IN predicate with subquery. It SELECTs customers having at least one unshipped order:

SELECT customers.name FROM customers 
WHERE customers.id IN 
(SELECT orders.custid FROM orders 
WHERE orders.status = 'unshipped');

This example uses an aggregate query that specifies a subquery with IN to find the maximum price of a book in the exclBookList:

SELECT MAX(price) FROM books WHERE id IN (SELECT id FROM exclBookList);

This example illustrates the use of a list of expressions with the IN predicate and a subquery.

SELECT * FROM t1 WHERE (x1,y1) IN (SELECT x2,y2 FROM t2);

This example illustrates the use of a list of expressions with the ANY predicate and a subquery.

SELECT * FROM t1 WHERE (x1,y1) < ANY (SELECT x2,y2 FROM t2);

The following example illustrates the use of a list of expressions with the ANY predicate.

Command> columnlabels on;
Command> SELECT * FROM t1;
X1, Y1
< 1, 2 >
< 3, 4 >
2 rows found.
Command> SELECT * FROM t2;
X2, Y2
< 3, 4 >
< 1, 2 >
2 rows found.

ANY / IN predicate (value list)

The ANY / IN quantified predicate compares an expression or list of expressions with a list of specified values. The ANY predicate evaluates to TRUE if one or more of the values in the ValueList relate to the expression or list of expressions as indicated by the comparison operator. Similarly, the IN predicate evaluates to TRUE if the expression or list of expressions is equal to one of the values in the list.

SQL syntax

RowValueConstructor {CompOp {ANY | SOME} | IN} ValueList

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstuctorList) |

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

The syntax for one element in the ValueList (no parentheses necessary):

ConstantExpression

The syntax for more than one element in the ValueList:

({ConstantExpression} [,...] )

The syntax for an empty ValueList:

( )

Parameters

Component Description
Expression Specifies a value to be obtained. The values in ValueList must be compatible with the expression. For information on the syntax of expressions, see "Expression specification".
ConstantExpression Specifies a constant value or an expression that evaluates to a constant value (such as a number, character string, or date). This includes support for bound values (? or :DynamicParameter), NULL, and calls to functions that return constant values.
= Is equal to.
<> Is not equal to.
> Is greater than.
>= Is greater than or equal to.
< Is less than.
<= Is less than or equal to.
{ANY|

SOME}

The predicate is TRUE if one or more of the values in the ValueList relate to the expression or list of expressions as indicated by the comparison operator. SOME is a synonym for ANY.
ValueList A list of values that are compared against the RowValueConstructor values.

The ValueList can be an empty list (sometimes generated by SQL generation tools) or consists of ConstantExpression entries.

The ValueList cannot include column references, sequences, subqueries, ROWID values, or ROWNUM values.

The ValueList can be nested if RowValueConstructor is a list. For example: (x, y) not in ((1+1, 2), (abs(-1), 5+1))


Description

Examples

Select all item numbers containing orders of 100, 200, or 300 items.

SELECT DISTINCT OrderItems.ItemNumber 
FROM OrderItems
WHERE OrderItems.Quantity = ANY (100, 200, 300)

Get part numbers of parts whose weight is 12, 16, or 17.

SELECT Parts.PartNumber FROM Parts 
WHERE Parts.Weight IN (12, 16, 17);

Get part number of parts whose serial number is '1123-P-01', '1733-AD-01', :SerialNumber or :SerialInd, where :SerialNumber and :SerialInd are dynamic parameters whose values are supplied at runtime.

SELECT PartNumber FROM Purchasing.Parts 
WHERE SerialNumber
IN ('1123-P-01', '1733-AD-01',:SerialNumber, :SerialInd);

To query an empty select list for IN condition:

SELECT * FROM t1 WHERE x1 IN ();

To use a list of expressions with IN:

SELECT * FROM t1 WHERE (x1,y1) IN ((1,2), (3,4));

The next three examples, using ttIsql, show the use of constant expressions. Assume a table T with a single column named X of type NUMBER, with the following data:

Command> select * from t;
< 1 >
< 2 >
< 3 >
< 4 >
< 5 >
5 rows found.

This first example uses constant expressions in a query:

Command> select x from t where x in (abs(1-2), to_number('1')+2, 3);
< 1 >
< 3 >
2 rows found.

This second example also uses dynamic parameters:

Command> select x from t where x = any (1+?, 1+3, ?);

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 '_QMARK_1' (NUMBER) > 1
Enter Parameter 2 '_QMARK_2' (NUMBER) > 5
< 2 >
< 4 >
< 5 >
3 rows found.

This third example also uses NULL:

Command> select x from t where x in (null, 1, 3+1, 2);
< 1 >
< 2 >
< 4 >
3 rows found.

For the next example, consider a table T_DATE with a single column named MYDATE of type DATE, with the following data:

Command> select * from t_date;
< 2013-08-13 00:00:00 >
< 2013-08-14 00:00:00 >
< 2013-08-15 00:00:00 >
3 rows found.

The example uses constant expressions for dates:

Command> select mydate from t_date where mydate in (date '2013-08-12'+1, 
date '2013-08-12'+2);
< 2013-08-13 00:00:00 >
< 2013-08-14 00:00:00 >
2 rows found.

For the next example, consider a table MYCHARS with a single column named COL1 of type VARCHAR2(32), with the following data:

Command> select * from mychars;
< abc >
< def >
< ghi >
3 rows found.

The example uses a function call that returns a constant expression:

Command> select col1 from mychars where col1 in (ltrim('abcdef', 'abc'));
< def >
1 row found.

The following example illustrates the use of a list of expressions for the IN predicate. The query returns the DEPARTMENT_NAME for departments with DEPARTMENT_ID = 240 and LOCATION_ID = 1700.

Note:

The expression on the right side of the IN predicate must be enclosed in double parentheses (( )).
Command> SELECT department_name FROM departments 
       > WHERE (department_id, location_id) IN ((240,1700));
< Government Sales >
1 row found.

BETWEEN predicate

A BETWEEN predicate determines whether a value is:

and:

The predicate evaluates to TRUE if a value falls within the specified range.

SQL syntax

Expression1 [NOT] BETWEEN Expression2 AND Expression3

Parameters

Parameter Description
Expression1, Expression2, Expression3 The syntax for expressions is defined in "Expression specification". Both numeric and non-numeric expressions are allowed in BETWEEN predicates, but all expressions must be compatible with each other.

Description

Examples

Parts sold for under $250.00 and over $1500.00 are discounted 25 percent.

UPDATE Purchasing.Parts 
SET SalesPrice = SalesPrice * 0.75
WHERE SalesPrice NOT BETWEEN 250.00 AND 1500.00;

Comparison predicate

A comparison predicate compares two expressions using a comparison operator. The predicate evaluates to TRUE if the first expression relates to the second expression as specified by the comparison operator.

SQL syntax

RowValueConstructor CompOp RowValueConstructor2

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstuctorList) | ScalarSubquery

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructor2 (one expression)

Expression

The syntax for RowValueConstructor2 (list of expressions)

((Expression[,...]))

The syntax for CompOp:

{= | <> | > | >= | < | <= }

Parameters

Component Description
Expression The syntax for expressions is defined under "Expression specification". Both numeric and non-numeric expressions are allowed in comparison predicates, but both expressions must be compatible with each other.
ScalarSubquery A subquery that returns a single value. Scalar subqueries and their restrictions are defined under "Subqueries".
= Is equal to.
<> Is not equal to.
> Is greater than.
>= Is greater than or equal to.
< Is less than.
<= Is less than or equal to.

Description

Examples

Retrieve part numbers of parts requiring fewer than 20 delivery days:

SELECT PartNumber FROM Purchasing.SupplyPrice 
WHERE DeliveryDays < 20;

The query returns the last_name of employees where salary=9500 and commission_pct=.25.

Note:

The expression on the right side of the equal sign must be enclosed in double parentheses (( )).
Command> SELECT last_name FROM employees 
       > WHERE(salary,commission_pct) = ((9500,.25));
< Bernstein >
1 row found.

The query returns the last_name of the employee whose manager_id = 205. The employee's department_id and manager_id is stored in both the employees and departments tables. A subquery is used to extract the information from the departments table.

Command> SELECT last_name FROM employees 
       > WHERE (department_id, manager_id) =
       > (SELECT department_id, manager_id FROM departments 
       > WHERE manager_id = 205);
< Gietz >
1 row found.

EXISTS predicate

An EXISTS predicate checks for the existence or nonexistence of a table subquery. The predicate evaluates to TRUE if the subquery returns at least one row for EXISTS or returns no rows for NOT EXISTS.

SQL syntax

[NOT] EXISTS (Subquery)

Parameters

The EXISTS predicate has the following parameter:

Parameter Description
Subquery The syntax of subqueries is defined under "Subqueries".

Description

Examples

Get a list of customers having at least one unshipped order.

SELECT customers.name FROM customers
WHERE EXISTS (SELECT 1 FROM orders 
WHERE customers.id = orders.custid 
AND orders.status = 'unshipped');

Get a list of customers having no unshipped orders.

SELECT customers.name FROM customers
WHERE NOT EXISTS (SELECT 1 FROM orders 
WHERE customers.id = orders.custid 
AND orders.status = 'unshipped');

IS INFINITE predicate

An IS INFINITE predicate determines whether an expression is infinite (positive infinity (INF) or negative infinity (-INF)).

SQL syntax

Expression IS [NOT] INFINITE

Parameters

Parameter Description
Expression Expression to test.

Description


IS NAN predicate

An IS NAN predicate determines whether an expression is the undefined result of an operation (that is, is "not a number" or NaN).

SQL syntax

Expression IS [NOT] NAN

Parameters

Parameter Description
Expression Expression to test.

Description


IS NULL predicate

The IS NULL predicate determines whether an expression has the value NULL. The predicate evaluates to TRUE if the expression is NULL. If the NOT option is used, the predicate evaluates to TRUE if the expression is NOT NULL.

SQL syntax

{ColumnName | Constant | Expression | LOBDataType} IS [NOT] NULL

Parameters

Parameter Description
ColumnName The name of a column from which a value is to be taken. Column names are discussed in Chapter 2, "Names, Namespace and Parameters".
Constant A specific value. See "Constants".
Expression Expression to test.
LOBDataType Value to test that is in a CLOB, BLOB, or NCLOB data type.

Examples

Use IS NULL to identify the president of the company, who is the only person without a manager.

Command> SELECT * FROM employees 
 > WHERE manager_id IS NULL;
< 100, Steven, King, SKING, 515.123.4567, 1987-06-17 00:00:00, AD_PRES, 24000, 
<NULL>, <NULL>, 90 >
1 row found.

The following statement uses IS NULL to identify all locations without a state or province.

Command> SELECT * FROM locations
       > WHERE state_province IS NULL;
< 1000, 1297 Via Cola di Rie, 00989, Roma, <NULL>, IT >
< 1100, 93091 Calle della Testa, 10934, Venice, <NULL>, IT >
< 1300, 9450 Kamiya-cho, 6823, Hiroshima, <NULL>, JP >
< 2000, 40-5-12 Laogianggen, 190518, Beijing, <NULL>, CN >
< 2300, 198 Clementi North, 540198, Singapore, <NULL>, SG >
< 2400, 8204 Arthur St, <NULL>, London, <NULL>, UK >
6 rows found.

LIKE predicate

A LIKE predicate evaluates to TRUE if the source contains a given pattern. The LIKE predicate matches a portion of one character value to another by searching the source for the pattern specified.

SQL syntax

Source [NOT] LIKE Pattern 
 [ESCAPE {'EscapeChar' | {? | :DynamicParameter} }]

The syntax for Pattern is as follows:

Expression [ || Expression ] [ ... ]

Parameters

Parameter Description
Source This source is searched for all occurrences of the pattern. The source may be an expression, column, character string resulting from a function, or any combination of these that results in a character string used for the source on which the pattern is matched. The source can be a CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. For more information on expressions, see Chapter 3, "Expressions". For more information on searching within a national character string within NCHAR, NVARCHAR, or NCLOB, see "Pattern matching for strings of NCHAR, NVARCHAR2, and NCLOB data types".
Pattern Describes a character pattern that you are searching for in the source with one or more expressions. The data type of the pattern should be a character string data type, such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Multiple expressions may be concatenated to form the character string used for the pattern.

The pattern consists of characters including digits and special characters. For example, NAME LIKE 'Annie' evaluates to TRUE only for a name of Annie with no spaces.

You can also use the predicate to test for a partial match by using one or more of the following symbols:

  • The symbol _ represents any single character. For example:

    BOB and TOM both satisfy the predicate NAME LIKE '_O_'.

  • The symbol % represents any string of zero or more characters. For example:

    MARIE and RENATE both satisfy the predicate NAME LIKE '%A%'.

You can use the _ or % symbols multiple times and in any combination in a pattern. However, you cannot use the symbols literally within a pattern unless you use the ESCAPE clause and precede the symbols with the escape character, described by the EscapeChar parameter.

Expression Any expression included in the pattern may be a column, a dynamic parameter, or the result of a function that evaluates to a character string. The syntax for expressions is defined in "Expression specification".
EscapeChar Describes an optional escape character which can be used to interpret the symbols _ and % literally in the pattern.

The escape character must be a single character. When it appears in the pattern, it must be followed by the escape character itself, the _ symbol or the % symbol. Each such pair represents a single literal occurrence of the second character in the pattern. The escape character is always case sensitive. The escape character cannot be _ or %.

?

DynamicParameter

Indicates a dynamic parameter in a prepared SQL statement. The parameter value is supplied when the statement is executed.

Description

Examples

Find each employee whose last name begins with 'Sm'.

Command> SELECT employee_id, last_name,first_name FROM employees
       > WHERE last_name LIKE 'Sm%'
       > ORDER BY employee_id,last_name,first_name;
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

Find each employee whose last name begins with 'SM'. This query returns no results because there are no employees whose last_name begins with upper case 'SM'.

Command> SELECT employee_id, last_name,first_name from employees
       > WHERE last_name LIKE 'SM%' 
       > ORDER BY employee_id,last_name,first_name;
0 rows found.

However, by upper casing the source value of the last name column, you can find all names that begin with 'SM'.

Command> SELECT employee_id, last_name, first_name FROM employees 
 > WHERE UPPER(last_name) LIKE ('SM%');
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

Use a dynamic parameter denoted by ? to find each employee whose last name begins with 'Sm' at execution time.

Command> SELECT employee_id, last_name,first_name FROM employees
       > WHERE last_name like ?
       > ORDER BY employee_id,last_name,first_name;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > 'Sm%'
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

Use a bind variable denoted by :a to find each employee whose last name begins with 'Sm' at execution time.

Command> SELECT employee_id, last_name,first_name FROM employees 
       > WHERE last_name LIKE :a 
       > ORDER BY employee_id,last_name,first_name;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 'A' (VARCHAR2) > 'Sm%'
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

For each employee whose last name begins with 'Smit', find the last name of the manager. Display the first name and last name of the employee and the last name of the manager.

Command> SELECT e1.first_name || ' ' || e1.last_name||' works for '||e2.last_name
       >    FROM employees e1, employees e2
       >    WHERE e1.manager_id = e2.employee_id
       >    AND e1.last_name like 'Smit';
< Lindsey Smith works for Partners >
< William Smith works for Cambrault >
2 rows found.

This query pattern references the last_name column as the pattern for which to search:

Command> SELECT e1.first_name || ' ' || e1.last_name||
> ' works for ' || e2.last_name
> FROM employees e1, employees e2
> WHERE e1.manager_id = e2.employee_id
> AND 'Smith' like e1.last_name;
< Lindsey Smith works for Partners >
< William Smith works for Cambrault >
2 rows found.

The pattern can be a column or the result of a function. The following uses the UPPER function on both the source last_name column as well as the 'ma' search string for which you are searching:

Command> SELECT last_name, first_name FROM employees
       > WHERE upper(last_name) LIKE UPPER('ma%');
< Markle, Steven >
< Marlow, James >
< Mallin, Jason >
< Matos, Randall >
< Marvins, Mattea >
< Mavris, Susan >
6 rows found.

The following query demonstrates using a dynamic parameter to request the pattern.

Command> SELECT first_name || ' ' || last_name 
 > FROM employees WHERE last_name like ?;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > 'W%'
< Matthew Weiss >
< Alana Walsh >
< Jennifer Whalen >
3 rows found.

The following query demonstrates combining a character string with a dynamic parameter in the pattern.

Command> SELECT first_name || ' ' || last_name 
 > FROM employees WHERE last_name like 'W' || ?;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > '%'
< Matthew Weiss >
< Alana Walsh >
< Jennifer Whalen >
3 rows found.

Pattern matching for strings of NCHAR, NVARCHAR2, and NCLOB data types

The LIKE predicate can be used for pattern matching for strings of type NCHAR, NVARCHAR2, and NCLOB. The pattern matching characters are:

Character Description
U+005F SPACING UNDERSCORE Represents any single Unicode character.
U+0025 PERCENT SIGN Represents any string of zero or more Unicode characters.

Description

Examples

In these examples, the Unicode character U+0021 EXCLAMATION MARK is being used to escape the Unicode character U+005F SPACING UNDERSCORE. Unicode character U+0025 PERCENT SIGN is not escaped, and assumes its pattern matching meaning.

VendorName is an NCHAR or NVARCHAR2 column.

SELECT VendorName FROM Purchasing.Vendors
WHERE VendorName LIKE N'ACME!_%' ESCAPE N'!';

This example is equivalent:

SELECT VendorName FROM Purchasing.Vendors
WHERE VendorName LIKE N'ACME!\u005F\u0025' ESCAPE N'!';