How to get the last letter of a string?

Question

How can I get the last letter of a string and check if it's vowel or consonant? I am using oracle 10g.

Here is what I came up with already:

SELECT last_name,
       Substr(last_name, -1, 1) "Last letter",
       Substr(last_name, 1, 1)  "First letter",
       CASE
         WHEN Substr(last_name, -1, 1) IN ( 'a', 'e', 'i', 'o', 'u' ) THEN
         'ends with a vowel'
         WHEN Substr(last_name, -1, 1) IN ( 'b', 'c', 'd', 'f',
                                            'g', 'h', 'j', 'k',
                                            'l', 'm', 'n', 'p',
                                            'q', 'r', 's', 't',
                                            'v', 'w', 'x', 'y', 'z' ) THEN
         'ends with a consonant'
       END                      "Last Letter Description",
       CASE
         WHEN Substr(last_name, 1, 1) IN ( 'a', 'e', 'i', 'o', 'u' ) THEN
         'starts with a consonant'
         WHEN Substr(last_name, 1, 1) IN ( 'b', 'c', 'd', 'f',
                                           'g', 'h', 'j', 'k',
                                           'l', 'm', 'n', 'p',
                                           'q', 'r', 's', 't',
                                           'v', 'w', 'x', 'y', 'z' ) THEN
         'starts with a consonant'
       END                      "First Letter Description"
FROM   employees
GROUP  BY first_name,
          last_name 

Now when you execute this on oracle 10g the "First Letter Description" is empty! What is wrong with my code?

Answer 1

Look at your data. Chances are the first character in employees.last_name is capitalized. Remember, Oracle is case sensitive. You can user UPPER() or LOWER() to help find your match.

Also it'd be more efficient to search just for vowels and use an else statement for to find exclusions as João suggests.

SELECT last_name,
       Substr(last_name, -1, 1) "Last character",
       Substr(last_name, 1, 1)  "First character",
       CASE
         WHEN lower(Substr(last_name, -1, 1)) IN ( 'a', 'e', 'i', 'o', 'u' ) THEN
         'ends with a vowel'
         ELSE
         'does not end with a vowel'
       END                      "Last Letter Description",
       CASE
         WHEN lower(Substr(last_name, 1, 1)) IN ( 'a', 'e', 'i', 'o', 'u' ) THEN
         'starts with a vowel'
         ELSE 
         'does not start with a vowel'
       END                      "First Letter Description"
FROM   employees
GROUP  BY first_name,
          last_name