[SQL문제풀기] Weather Observation Station 7

문제

Weather Observation Station 7 | HackerRank

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION.

https://www.hackerrank.com/challenges/weather-observation-station-7/problem?isFullScreen=true

MySQL

내가 작성한 정답1 - like

select distinct city
from station
where city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u';

내가 작성한 정답2 - 정규표현식

select distinct city
from station
where city regexp '[aeiou]$'; // $: 문자열의 끝

내가 작성한 정답3 - substring, substr 둘 다 사용가능

select distinct city
from station
where substr(city,-1,1) in ('a','e','i','o','u');

select distinct city
from station
where substring(city,-1,1) in ('a','e','i','o','u');

내가 작성한 정답4 - right

select distinct city
from station
where right(city,1) in ('a','e','i','o','u');

Oracle

내가 작성한 정답1 - like

select distinct city
from station
where city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u';

내가 작성한 정답2 - 정규표현식 regexp_like

select distinct city 
from station
where regexp_like(city,'[aeiou]$');

내가 작성한 정답3 - substr

select distinct city
from station
where substr(city,-1,1) in ('a','e','i','o','u');

MS SQL Server - MySQL과 유사

내가 작성한 정답1 - like

select distinct city
from station
where city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u';

내가 작성한 정답2 - like2

select distinct city
from station
where city like '%[aeiou]';

substring은 사용 불가 → 음수 지원 안됨

내가 작성한 정답3 - right

select distinct city
from station
where right(city,1) in ('a','e','i','o','u');

DB2 - Oracle과 유사

내가 작성한 정답1 - like

select distinct city
from station
where city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u';

substr - 음수지원 안됨

정리!