SQL 기초(9) - 문자 함수

hackerrank- Weather Observation Station11

Left,Right,in, not in, like, not like를 이용해보기

• 모음으로 시작하지 않거나 모음으로 끝나지 않는 도시의 이름을 찾으시오.

select distinct city #중복되는 값 제거
from station
where 
left(lower(city),1) not in ('a','e','i','o','u')
or right(lower(city),1) not in ('a','e','i','o','u');

아래 방법도 가능할 것 같으나 중간에 or을 섞는 방법을 모름.

WHERE LOWER(column_name) NOT LIKE 'a%'
AND LOWER(column_name) NOT LIKE 'e%'
AND LOWER(column_name) NOT LIKE 'i%'
AND LOWER(column_name) NOT LIKE 'o%'
AND LOWER(column_name) NOT LIKE 'u%'
AND LOWER(column_name) NOT LIKE '%a'
AND LOWER(column_name) NOT LIKE '%e'
AND LOWER(column_name) NOT LIKE '%i'
AND LOWER(column_name) NOT LIKE '%o'
AND LOWER(column_name) NOT LIKE '%u';

chat GPT 선생님께 여쭈어보니 아래와 같은 코드를 짜주었다... 완벽,,,

SELECT city
FROM station
WHERE NOT (LOWER(city) LIKE 'a%' 
           OR LOWER(city) LIKE 'e%' 
           OR LOWER(city) LIKE 'i%' 
           OR LOWER(city) LIKE 'o%' 
           OR LOWER(city) LIKE 'u%')
OR NOT (LOWER(city) LIKE '%a' 
        OR LOWER(city) LIKE '%e' 
        OR LOWER(city) LIKE '%i' 
        OR LOWER(city) LIKE '%o' 
        OR LOWER(city) LIKE '%u');