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

문제

제출

SELECT
  SUBSTR(address, 1, INSTR(address, ' ') - 1) AS sido, -- #1
  SUBSTR(
    address,
    INSTR(address, ' ') + 1,
    INSTR(SUBSTR(address, INSTR(address, ' ') + 1), ' ') - 1 -- #2
  ) AS sigungu, 
  count(cafe_id) cnt
FROM
  cafes
GROUP BY
  1,
  2
ORDER BY
  3 desc

#1. 1번째 문자부터 공백(' ') 전까지의 자릿수 구한다
#2. address 컬럼에서 공백(' ') 다음 문자부터 다음 공백(' ') 전까지의 문자의 자릿수 구한다.

INSTR(SUBSTR(address, INSTR(address, ' ') + 1), ' ') - 1 

address 컬럼에서 첫 공백 다음 문자부터 끝까지 가져오고(SUBSTR(address, INSTR(address, ' ') + 1) ), 다음 공백까지의 자리수 -1 한다.

📌 새로 배운 내용

✅ INSTR()

문자열 안에서 특정 문자의 위치를 찾아 반환하는 함수

INSTR(문자열, 찾을 문자열)

• 문자열: 검색할 대상 문자열 (컬럼명 또는 직접 입력 가능)
• 찾을 문자열: 찾고 싶은 문자열 (한 글자 또는 여러 글자 가능)
• 반환값: 찾을 문자열이 처음 등장하는 위치 (1부터 시작), 찾을 문자열이 없으면 0 반환

예시

SELECT INSTR('Hello, World!', 'W');

→ 8

찾을 문자열 없을 때

SELECT INSTR('Hello, World!', 'x');

→ 0

SUBSTR()와 함께 사용 - 공백 전까지 문자열 가져오기

SELECT SUBSTR('123 Main Street', 1, INSTR('123 Main Street', ' ') - 1);

→ 123
INSTR()로 첫 번째 공백 위치 찾고, SUBSTR()로 그 전까지 잘라낸다.

✅ SUBSTR()

문자열에서 특정 부분을 잘라서 반환하는 함수

SUBSTR(문자열, 시작 위치, [길이])

• 문자열: 잘라낼 대상 문자열 (컬럼명 또는 직접 입력한 문자열 가능)
• 시작 위치: 문자열에서 시작할 위치 (1부터 시작)
• 길이 (선택): 가져올 문자 개수 (생략하면 끝까지 반환)

예시

SELECT SUBSTR('Hello, World!', 8);

→ World!
8번째 문자부터 끝까지 반환

음수 인덱스 사용

문자열 끝에서부터 거꾸로 센다.

SELECT SUBSTR('Hello, World!', -6, 5);

→ World

또 다른 답

SELECT
  substr(address, 1, instr(address, ' ') -1) sido,
  substr(
    substr(address, instr(address, ' ') + 1),
    1,
    instr(substr(address, instr(address, ' ') + 1), ' ') -1
  ) sigungu,
  count(cafe_id) cnt
FROM
  cafes
group by
  1,
  2
ORDER BY
  3 desc