Iin Oracle how can I display the LENGTH of the last_name for all employees whose names start with the letter "A" "J" "M"

by Ayah Blessing
(Nigerian/ Rivers/ Port harcourt)

From Ayah, Port Harcourt

I am working with Oracle 11G and want to display the last name of an employee from the employees table,with the first letter in uppercase and the rest in lowercase. That I have done, but I want to display the LENGTH of the last name for all employees whose names start with the letter "A" ,"M" ,"J". Thank you.

There are at least 3 ways to do this in Oracle SQL:
  1. use a case statement
  2. use the decode function
  3. use the WHERE clause to restrict the rows selected
We'll combine the first 2 methods into the same query to save space.

SELECT initcap(last_name) last_name
      ,CASE WHEN substr(initcap(last_name),1,1) IN ('A','M','J')
               THEN length (last_name)
       END using_case
      ,DECODE(substr(initcap(last_name),1,1)
                  ,'A',length (last_name)
                  ,'M',length (last_name)
                  ,'J', length (last_name)
                  ) using_decode
FROM employees

This query will return something like the following using the employees table in the sample HR schema provided with Oracle (only partial results have been shown).

LAST_NAME            USING_CASE USING_DECODE
------------------ ------------ --------------
Abel                                    4                  4
Ande                                   4                  4
Atkinson                              8                  8
Austin                                 6                  6
Baida
Banda
Bates
Bell
.
.
.
Johnson                                7                 7
Jones                                   5                 5


As we can see this Oracle SQL returns the employee's last name with the first letter in upper case, then uses a case statement and a decode statement to return the length of the employee's name if it starts with 'A','M' or 'J'.

Using the WHERE clause to restrict the rows returned from our table would give us a query something like the following:

SELECT initcap(last_name) last_name
      ,length (last_name) name_length
FROM employees
WHERE substr(initcap(last_name),1,1) IN ('A','M','J')

And the results would be the same as before assuming the data was the same, except that this time there would be no NULL values for those employees whose names do not start with 'A', 'M', or 'J' as there would be with previous query.


LAST_NAME              USING_CASE USING_DECODE

--------------------- ------------ --------------
Abel                                        4                  4
Ande                                       4                  4
Atkinson                                  8                  8

Austin                                     6                  6

Johnson                                   7                  7
Jones                                      5                  5

You might also be interested in this question and answer on SQL what is the difference between a query and a subquery and in our SQL tutorials.

If you're looking for more formal training on Oracle, why not take a training course with one of our partners? See our Oracle training page for more details.

Comments for Iin Oracle how can I display the LENGTH of the last_name for all employees whose names start with the letter "A" "J" "M"

Average Rating starstarstarstarstar

Click here to add your own comments

Sep 09, 2010
Rating
starstarstarstarstar
EASY WAY
by: SATYENDRA SHUKLA

SELECT LAST_NAME,LENGTH(LAST_NAME)
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'A%'
OR LAST_NAME LIKE 'J%'
OR LAST_NAME LIKE 'M%';

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.