Other Operators

1.Concatenation Operator

The concatenation operator manipulates character strings and CLOB data.

Operator      Purpose
||                    Concatenates character strings and CLOB data.

EX:

SELECT NAME || ‘ Belongs to ‘ || city FROM PERSONS ORDER BY NAME;

2.Hierarchical Query Operators

Two operators, PRIOR and CONNECT_BY_ROOT, are valid only in hierarchical queries.

PRIOR

In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator. PRIOR evaluates the immediately following expression for the parent row of the current row in a hierarchical query. PRIOR is most commonly used when comparing column values with the equality operator.

CONNECT_BY_ROOT

It is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the  column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.

Restriction on CONNECT_BY_ROOT

You cannot specify this operator in the START WITH condition or the CONNECT BY condition.

3.User-Defined Operators

user-defined operators act like the built-in operators such as , and = . However, We can create them with the CREATE OPERATOR  statement, and they are identified by user-defined names. They reside in the same namespace as tables, views, types, and standalone
functions.

After you have defined a new operator, you can use it in SQL statements like any other built-in operator.

Ex:-

CREATE OPERATOR Contains  BINDING (VARCHAR2, VARCHAR2)  RETURN NUMBER  USING text.contains;

Advertisements