Recipe1.13.Searching for Patterns

Recipe 1.13. Searching for Patterns

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".