Divide events according to whether their first/last letters are the same or vowels

first I created view for simplicty then use logic,

/*Create view table1

as

select EventName,lower(left(eventname,1)) as lefty,lower(right(eventname,1)) as righty from tblEvent*/

select * from

(select eventname,

Case

 when lefty=righty

 then 'Same letter'

        when righty in ('a','e','i','o','u') and lefty in ('a','e','i','o','u')

 then 'Begin and ends with vowel'

 end as 'verdict'

from table1) as t

where verdict is not null

Query_Sol. for MySQL users

-- Important: in MySQL, '[]' wildcard do not work with LIKE function
-- used left(), right() function to extracts a number of characters from a string

SELECT EventName, 
CASE 
    WHEN LEFT(EventName, 1) IN('A','E','I','O','U') AND RIGHT(EventName, 1) IN('a','e','i','o','u') THEN 'Begins and ends with vowels'
    WHEN LEFT(EventName, 1) = RIGHT(EventName, 1) THEN 'Same letter'
END AS Verdict
FROM tblevent
HAVING Verdict IS NOT NULL
ORDER BY EventDate;

This works as well.

SELECT
    EventName,
    CASE 
    WHEN SUBSTRING(EventName, 1,1)=SUBSTRING(EventName, LEN(EventName)-0,LEN(EventName)-0) THEN 'SAME LETTER'
    WHEN SUBSTRING(EventName, 1,1) IN ('A','E','I','O','U') AND SUBSTRING(EventName, LEN(EventName)-0,LEN(EventName)-0) IN ('A','E','I','O','U') THEN 'BEGINS AND END WITH A VOWEL'
    
    END AS Verdict from tblEvent 
    WHERE 
    SUBSTRING(EventName, 1,1)=SUBSTRING(EventName, LEN(EventName)-0,LEN(EventName)-0) OR 
    (SUBSTRING(EventName, 1,1) IN ('A','E','I','O','U') AND SUBSTRING(EventName, LEN(EventName)-0,LEN(EventName)-0) IN ('A','E','I','O','U'))
 ORDER BY EventDate ASC
 

I think the following query should work too!

SELECT EventName
        ,CASE WHEN EventName LIKE '[AEIOU]%' AND EventName LIKE '%[aeiou]' THEN 'Begin and ends with vowel'
        ELSE 'Same Letter' END AS Verdict
FROM tblEvent
Order BY Verdict

I added in the default of "Boring event" but this can be left out.

select EventName, case when left(eventname, 1) = right(eventname, 1) then 'Same letter'
                       when left(eventname, 1) like '[aeiou]' and right(eventname, 1) like '[aeiou]' then 'Begins and ends with a vowel'
                       else 'Boring event' end as Verdict
from tblevent
order by Verdict

SELECT * FROM (
SELECT EventName
        , CASE
            WHEN LOWER(LEFT(EventName,1)) IN ('a','e','i','o','u') AND LOWER(RIGHT(EventName,1)) IN ('a','e','i','o','u')
                THEN 'Begins and ends with vowle'
            WHEN LOWER(LEFT(EventName,1)) = LOWER(RIGHT(EventName,1))
                THEN 'Same letter'
          END AS Verdict
FROM tblEvent ) AS T
WHERE Verdict IS NOT NULL
 

NOTE: The QUESTION IS ORDERD BY EVENTDATE, if anyone was wondering the order.

If anyone could verify if this is correct, because it seem uncomfortably long:

SELECT
EventName,
CASE
    WHEN LEFT(EventName, 1) = RIGHT(EventName, 1) 
        THEN 'Same letter'
    WHEN (LEFT(EventName, 1) = 'a'  OR LEFT(EventName, 1) = 'e' OR LEFT(EventName, 1) = 'i' OR LEFT(EventName, 1) = 'o' OR LEFT(EventName, 1) = 'u') AND (RIGHT(EventName, 1) = 'a' OR RIGHT(EventName, 1) = 'e' OR RIGHT(EventName, 1) = 'i' OR RIGHT(EventName, 1) = 'o' OR RIGHT(EventName, 1) = 'u') 
        THEN 'Begins and ends with vowel' 
        
    END as 'Verdict' 

FROM
tblEvent
WHERE (CASE
    WHEN LEFT(EventName, 1) = RIGHT(EventName, 1) 
        THEN 'Same letter'
    WHEN (LEFT(EventName, 1) = 'a'  OR LEFT(EventName, 1) = 'e' OR LEFT(EventName, 1) = 'i' OR LEFT(EventName, 1) = 'o' OR LEFT(EventName, 1) = 'u') AND (RIGHT(EventName, 1) = 'a' OR RIGHT(EventName, 1) = 'e' OR RIGHT(EventName, 1) = 'i' OR RIGHT(EventName, 1) = 'o' OR RIGHT(EventName, 1) = 'u') 
        THEN 'Begins and ends with vowel' 
        
    END ) IS NOT NULL

ORDER BY
EventDate