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

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

Transforming Nulls into Real Values - SQL

Problem

You have rows that contain nulls and would like to return non-null values in place of those nulls.

Solution

Use the function COALESCE to substitute real values for nulls:

 1 select coalesce(comm,0)
 2   from emp

Discussion

The COALESCE function takes one or more values as arguments. The function returns the first non-null value in the list. In the solution, the value of COMM is returned whenever COMM is not null. Otherwise, a zero is returned.

When working with nulls, it's best to take advantage of the built-in functionality provided by your DBMS; in many cases you'll find several functions work equally as well for this task. COALESCE happens to work for all DBMSs. Additionally, CASE can be used for all DBMSs as well:

 select case
        when comm is null then 0
        else comm
        end
   from emp

While you can use CASE to translate nulls into values, you can see that it's much easier and more succinct to use COALESCE.

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