How to find out the last name of employees that have third letter starts with a?

by Adil Khan
(Delhi)

How to find out the last name of employees that have third letter starts with a?


I used this query because single underscore used for one character space so I have given double underscore but it is not showing proper output plz help me.

SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE '__a';


There's only 1 thing wrong with your query and that is that you forgot about the rest of the name. As it stands, your SQL statement, retrieves the last_name column and compares it to a string 3 characters long. Therefore any name that is longer than 3 characters won't match. So how do you fix this? Simple, just add a wild card ('%') to the end of your search pattern, so your SQL would look like this:

SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE '__a%';

This returns the following results from the sample hr database.

LAST_NAME
--------------
Grant
Grant
Whalan


Of course, as always with SQL there is more than one way to do this. Another way of achieving the same answer would be to use the substr function as per the following SQL statement

SELECT LAST_NAME FROM EMPLOYEES WHERE substr(LAST_NAME,3,1) = 'a';

In this example we extract just the 3rd character from the last_name column and compare it to a.

Since Oracle 10G, Oracle has also supported regular expressions which can be fiendishly complex and difficult to understand but they can also make your queries much simpler as you can check complex patterns with just one statement. Regular expressions first appeared with UNIX for use with programs including sed and grep so anyone familiar with Unix or Linux should have little trouble with them. Regular expressions are based on meta characters (i.e. characters that have special meanings). The most common ones are '^' which means the beginning of the string (before the first character), '$' which refers to the end of the string, '.' which represents any character and '*' which means repeat the previous character 0 or more times. The full list of meta characters and their meanings are in the Oracle Database SQL Language Reference manual.

Therefore, using regular expressions are query would become:

SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME REGEXP_LIKE '^..a.*';

In this case we're saying that the last_name column can start with any 2 characters (as indicated by the pattern "^.."), then have an "a", followed by zero or more occurrences of any character (as indicated by the pattern ".*").

This returns exactly the same results as the other 2 queries.

There is more on using regular expressions in SQL here and on using regular expression sin pl/sql here.

For more help with SQL see our SQL tutorials.

Also see the
Oracle training page for details of our cost-effective Oracle training both on-line and off-line.

Click here to post comments

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