Skip to main content

LIKE operator in ORACLE

Wild Charcters :-

These characters are used to provide pattern matching which means comparision to data is provided with partial value.

ORACLE provides the following wild characters.

           i)    _(Underscore) -> Represents single character.
          ii)    %(Percentage) -> Represents group of character.

Ø  Inorder to have wild characters for the comparision to partial data, LIKE operator is used.

Ø  If these wild characters are formed in data, then the wild characters can be converted to data using ‘\’ (backslash). It is represented through an option called ‘ESCAPE’ , which is written after like operator.

Examples :-

Display employee number and employee name of all those employees whose names contain 4 characters.

Ans : select empno, ename from emp
          Where ename LIKE ‘----‘;

Display ename, job of all those employees whose name starts with ‘A’.

Ans : select empno, ename from emp
          Where ename LIKE ‘A%’;

Display ename, job of all those employees whose name ends with ‘S’.

Ans : select empno, ename from emp


          Where ename LIKE ‘%S’;

Display ename, job, sal of all those employees whose name contain ‘A’.

Ans : Select ename, job, sal from emp
          Where ename LIKE ‘%A%’;

Display ename, sal of all those employees whose salary is ending with ’00’.

Ans : Select ename,sal from emp
          Where sale LIKE ‘’;

Display ename, hiredate of all those employees who have been hired in the year 81.

Ans : Select ename, hiredate from emp
          Where hiredate LIKE ‘%81’;

Display empno, ename, hiredate of all those employees who have been hired in those months whose name doesn’t starts with ‘A’.

Ans : Select empno, ename, hiredate from emp
          Where hiredate NOT LIKE  ‘_ _ _ _A%’;

Display all those employees whose designation contains ‘_’.

Ans : Select * from dept
          Where dname like ‘%\_%’ ESCAPE ‘\’;


Labels:

Comments

Post a Comment

Popular posts from this blog

About Oracle Buffer

About Oracle Buffer :- All Commands of SQL are typed at the SQL prompt. Only one SQL statement is managed in the SQL buffer. The current SQL statement replaces the previous SQL statement in the buffer. The SQL statements can be divided into different lines within the SQL buffer. Only one line i.e, the current line can be active at a time in the SQL buffer. At SQL prompt, editing is possible only in the current SQL buffer line. Every statement of SQL should be terminated using semi colon " ; ". One sql statement can contain only one semi colon. To run the previous or current SQL statement in the bufer type " / " at SQL prompt. To open the SQL editor type " ED " at SQL prompt.
Post a Comment
Read more

Oracle Object Oriented Concepts

An Object is a reusable application component that developers need to be aware of, rather than how it works. Object are basic entities in a system. They could represent a person, place, bank account, or any item that is handled by program. Every object consists of an attribute and one or more methods. An attribute could be any property of the object. Class:           It is a collection of attributes and functions (method) to plan the object. Object Table : ·    Object table are created by using the user defined data types. ·        In an object table each row or record is treated as an object. ·        Each row in an object table has an object Identified (OID), which is unique through out       the   database. ·       The rows or objects of an object table can be referenced by other objects with in the  database. ·    ...
Post a Comment
Read more

Filtering and Sorting data using SQL statements

  Filtering & Sorting Data using SQL     Once you know the basics ( SELECT , FROM , WHERE ), filtering and sorting helps you get the exact rows from tables .   1. Filtering with Conditions ( AND , OR , NOT )  - Use WHERE to filter rows. - Combine with logical operators :     • AND: all conditions true      • OR: any condition true      • NOT: negate condition   - - All conditions true (AND) SELECT *  FROM employees WHERE department = 'Sales'    AND salary > 50000; -- Any condition true (OR) SELECT *  FROM employees WHERE department = 'Sales'     OR department = 'Marketing'; -- Negate condition (NOT) SELECT *  FROM employees WHERE NOT department = 'HR';   2. Pattern Matching with LIKE   - % = any sequence, _ = single char     • Names starting with 'J'      • Names with 4 letters starting with 'J'   - - N...
Post a Comment
Read more