Searching for Patterns - SQL

Problem You want to return rows that match a particular substring or pattern. Consider the following query and result set: select ename, job from emp where deptno in (10,20) ENAME JOB ---------- --------- SMITH CLERK JONES MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT ADAMS CLERK FORD ANALYST MILLER CLERK Of the employees in departments 10 and 20, you want to return only those that have either an "I" somewhere in their name or a job title ending with "ER": ENAME JOB ---------- --------- SMITH CLERK JONES MANAGER CLARK MANAGER KING PRESIDENT MILLER CLERK Solution Use the LIKE operator in conjunction with the SQL wildcard operator ("%"): 1 select ename, job 2 from emp 3 where deptno in (10,20) 4 and (ename like '%I%' or job like '%ER') Discussion When used in a LIKE pattern-match operation, the percent ("%") operator matches any sequence of characters. Most SQL implementations also provide the underscore ("_") operator to match a single character. By enclosing the search pattern "I" with "%" operators, any string that contains an "I" (at any position) will be returned. If you do not enclose the search pattern with "%", then where you place the operator will affect the results of the query. For example, to find job titles that end in "ER", prefix the "%" operator to "ER"; if the requirement is to search for all job titles beginning with "ER", then append the "%" operator to "ER".

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table