a single query where the ename starts with or contains 'paul'

by susan brown
(texas)

I need to write a single Oracle query without using AND/OR where the ename may starts with or may contain 'paul'.

Comments for a single query where the ename starts with or contains 'paul'

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 03, 2016
Rating
starstarstarstarstar
How to find partial matches in SQL
by: John

This is quite a common task in SQL and is something the language handles well. The objective is to retrieve all rows that match either exactly or partially with a known character string. In this case the character string is a constant but if the query is embedded in Oracle PL/SQL or we are using SQL*Plus or any of the modern GUI database tools (such as TOAD or SQL Developer) we can use a variable.

As per usual with SQL there is more than one way of writing queries like this. The first method is to use the keyword "LIKE" in pace of the equals sign in the WHERE clause that we would use if we were interested in only exact matches. Let's look at an example.

SELECT * FROM emp WHERE ename LIKE '%PAUL%';

The above query selects all columns from all rows in the emp table in our Oracle database where the ename column contains the letters "PAUL" in that order. In other words it would match "PAULA". "PAULINE", "JOHN-PAUL" as well as "PAUL".

One important point to remember when comparing
character strings is that the matching is case sensitive. This means that if the names were stored in mixed case in our database we wouldn't find a match, so "Paul", "Pauline", "Paula" would not match. To avoid this we would need to change our query to be case-insensitive by converting the ename column to uppercase before the comparison, like so:-

SELECT * FROM emp WHERE UPPER(ename) LIKE '%PAUL%';


Here, the "upper" function converts the ename column to upper case before the comparison is made with our string. Of course if you want a case sensitive search then you would leave that out.

The second way of querying the employee table in our Oracle database to find those employees whose name contains 'PAUL' is to use the INSTR function as in the next example

SELECT * FROM emp WHERE INSTR(UPPER(ename),'PAUL')>0;


The INSTR function is designed to return the position of the substring in the string. In this case it will provide the position of the substring "PAUL" in ename. However we are not interested in the actual position, just in whether it is found which is signaled by a return value of 1 or more. Again to make the search case insensitive we ensure that the value of ename is in upper case before we compare it to our substring.

The third method of writing a query to return employee names containing 'PAUL' from our Oracle database is to use regular expressions. These will be familiar to anyone who knows UNIX, Linux or Perl but for those who are not, regular expressions allow the use of meta characters (characters with special meanings) in the searched-for string to provide a more precise match. Often, regular expressions are used for data validation for phone numbers or postal codes for example.

Oracle allows the use of regular expressions with the following 5 functions:

  • REGEXP_COUNT
  • REGEXP_INSTR
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR

There isn't room to go into a detailed discussion of the use of regular expressions here so see the Oracle Database Advanced Application Developer's Guide for more information.

We can actually use any of these functions to achieve our objective as the following examples demonstrate.

SELECT * FROM EMP where REGEXP_REPLACE(ename,'.*PAUL.*','Z',1,0,'i') = 'Z';

In the above example '.*PAUL.*' has the effect of replacing any ename containing "PAUL" with the letter "Z". That is the whole string is replaced with the letter 'Z'. The final parameter in the function call - 'i' - indicates a case-insensitive search. The function result is compared to 'Z' to see if it matches. If it does the original ename obviously contained either the string 'PAUL' or the name was 'Z'. To guard against a false match in the unlikely event that the employee's name was 'Z' we could use special characters or numbers instead. However this Oracle SQL function is not really designed for just simple string matching so we would be better off using one of the others.

The next example demonstrates that we don't need to use any meta characters with these functions if we want to be very specific about the searched-for string.

SELECT * FROM EMP where REGEXP_INSTR(ename,'PAUL',1,1,0,'i') > 0;

In the following example, even though the search is case insensitive, Oracle will return whatever case the original ename had, so for the comparison to work we need to convert to upper case. This example also demonstrates again that just because you can do something it doesn't mean that you should as there are simpler ways of doing this.

SELECT * FROM EMP where UPPER(REGEXP_SUBSTR(ename,'PAUL',1,1,'i'))
='PAUL';


The following example is a case insensitive search equivalent to using the regular LIKE clause with UPPER.

SELECT * FROM emp WHERE REGEXP_LIKE(emp_name,'BILL','i');

In the final example, REGEXPR_COUNT returns the number of occurrences of the substring in the string but as with INSTR we're interested in any value of one or more (a return of zero indicates that no match was found).

SELECT * FROM emp WHERE REGEXP_COUNT(emp_name,'BILL',1,'i')>0

For more help with SQL see our series of Oracle SQL tutorials. Also see our Oracle training page if you're interested in formal training courses, either on-line or in person.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.