문제

전국 카페 주소 데이터 정제하기

코드

SELECT
  substr(address, 1, instr(address, ' ')-1) as "sido"
  , substr(address
    , instr(address, ' ')+1
    , instr(substr(
        address
        , instr(address, ' ')+1)
        , ' ')-1
    ) as "sigungu"
  , count(*) as "cnt"
FROM
  cafes
GROUP BY
  1, 2
ORDER BY
  cnt desc
;  

알게된 점

python에서는 split() 함수로 쉽게 할 수 있었지만, sql에서는 (적어도 sqlite에서는) split 함수가 없다
그렇기에 다소 우회적인 방법을 택할 수 밖에 없었는데, instr() 함수로 특정 구분자의 index값을 받고, 이를 기준으로 substr() 함수를 사용하는 것이다
[sqlite] 문자열 자르기 그 외 팁

이것만 알면, 그 이후에는 아래와 같은 과정으로 코드를 작성했다

(1) instr 함수를 통해 ‘ ‘의 index를 찾는다
(2) 해당 index를 기준으로 문자를 자른다
(3) 그 앞은 sido로 들어간다
(4) 그 뒤의 부분을 다시 instr 함수로 ‘ ‘를 찾아낸다
(5)다시 한번 더 문자를 자르고, 이번엔 sigungu로 넣는다

하나 주의할 것은, substr() 함수를 사용할 때 index를 제대로 적용해야 한다는 것이다
처음에는 -1을 넣지 않았는데, 그렇다보니 '경기도'가 아니라 '경기도 '가 출력되어서 오답으로 판정되곤 했다