[SQL코드카타] 138번 ~ 143번 Hackerrank

> about 정규표현식

(138번) weather-observation-station-6

> 처음에는 IN ( 'A%') 되나??  LIKE 여러번 써야 하나?? 비효율적인데.. 싶었는데

> 다른 사람들의 답을 보며, 두가지 유용한 방법을 알게 됨.

SELECT DISTINCT CITY 
FROM STATION 
WHERE SUBSTR(CITY, 1, 1) IN ('a', 'e', 'i', 'o', 'u');
-- 대-소문자 구분시 UPPER OR LOWWER 사용

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY REGEXP '^[AEIOUaeiou].*'

 

 

(139번) weather-observation-station-7

-- ORACLE
SELECT DISTINCT CITY 
FROM STATION 
WHERE SUBSTR(CITY,-1,1) IN('a','e','i','o','u');

#MySQL
SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY REGEXP '[AEIOUaeiou]$'

 

---- 아래 문제부터 my sql 사용 ---

(140번) weather-observation-station-8

SELECT DISTINCT CITY 
FROM STATION 
WHERE SUBSTRING(CITY,1,1) IN ('A','E','I','O','U')
       AND RIGHT(CITY,1) IN ('A','E','I','O','U')

> SUBSTRING / LEFT / RIGHT

 

(141번) weather-observation-station-9

SELECT DISTINCT CITY 
FROM STATION 
WHERE SUBSTRING(CITY,1,1) NOT IN ('A','E','I','O','U','a','e','i','o','u')

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY REGEXP '^[^AEIOUaeiou]'

 

(142번) weather-observation-station-10

SELECT DISTINCT CITY 
FROM STATION 
WHERE SUBSTRING(CITY,-1,1) NOT IN ('A','E','I','O','U','a','e','i','o','u')

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '[^AEIOUaeiou]$'

 

(143번) weather-observation-station-11

SELECT DISTINCT CITY 
FROM STATION 
WHERE SUBSTRING(CITY,-1,1) NOT IN ('A','E','I','O','U','a','e','i','o','u')
      OR LEFT(CITY,1) NOT IN ('A','E','I','O','U','a','e','i','o','u')

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY NOT REGEXP '^[AEIOUaeiou].*[AEIOUaeiou]$'

 

(144번) weather-observation-station-12

SELECT DISTINCT CITY 
FROM STATION 
WHERE SUBSTRING(CITY,-1,1) NOT IN ('A','E','I','O','U','a','e','i','o','u')
      AND LEFT(CITY,1) NOT IN ('A','E','I','O','U','a','e','i','o','u')

SELECT DISTINCT city 
FROM station 
WHERE 1=1
      AND city REGEXP '^[^aeiou].*[^aeiou]$'
 -- 맨 앞(^)에 a또는--u가 아닌(^) 그리고 아무 문자(.)가 0또는 1회이상(*) a또는--u가 맨 뒤($)에 아닌