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 two ways to do this in Oracle SQL:

1. use a case statement
2. use the decode function

We'll combine both these 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
Baer
Baida
Banda
Bates
Bell
.
.
.
Johnson                                    7                 7
Jones                                       5                 5


As we can see this Oracle SQL returns the employees last name with the first letter as 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'.