SQL 쿼리로 URL에서 파라미터 추출하기 : SUBSTRING_INDEX 활용법

👉 URL 형태와 원하는 결과

• 테이블의 URL 컬럼에 URL이 저장되어 있는 상태
  • www.example.com/type=sale12&order_num=ex12345
    • ⇒ 여기서 type의 값인 sale12 추출
  • www.example.com/type=sale29
    • ⇒ 여기서 type의 값인 sale29 추출
      
      
      
      

👉 쿼리 예제

SELECT DATE(SAL_DTM) AS `결제일자`
     , SUBSTRING_INDEX(SUBSTRING_INDEX(URL, 'type=', -1), '&', 1) AS `판매 종류`
     , COUNT(DISTINCT USER_ID) AS `구매자수`
  FROM BOOK_STORE
 GROUP BY `결제일자`, `판매 종류`
 ORDER BY `결제일자`, `판매 종류`
;

• 첫 번째 SUBSTRING_INDEX 함수
  • SUBSTRING_INDEX(URL, 'type=', -1)
    • 이 함수의 기준은 URL 필드의 값
    • 'type=' 문자열을 찾아 이 문자열 이후의 모든 내용을 추출
    • -1은 함수가 'type=' 문자열을 찾은 후 그 뒤에 오는 문자열을 반환 시킴
      • sale12&order_num=ex12345를 반환
• 두 번째 SUBSTRING_INDEX 함수
  • SUBSTRING_INDEX(첫 번째 함수의 결과, '&', 1)
    • 이 함수는 & 문자를 찾아 그 앞에 있는 모든 내용을 추출
    • 1은 함수가 문자열의 시작부터 '&' 문자가 처음 나타나기 전까지의 부분을 반환 시킴
      • sale12를 반환
        
        
        
        

👉 SUBSTRING_INDEX

• 문자열에서 하위 문자열을 반환
• 기본 형태는 SUBSTRING_INDEX(문자열, 구분자, 구분자 인덱스)
  • 인덱스가 양수인 경우
    • 왼쪽부터 계산해서 해당 구분자의 왼쪽을 반환

      SELECT SUBSTRING_INDEX('www.mariadb.org', '.', 2);
      +--------------------------------------------+
      | SUBSTRING_INDEX('www.mariadb.org', '.', 2) |
      +--------------------------------------------+
      | www.mariadb                                |
      +--------------------------------------------+

  • 인덱스가 음수인 경우
    • 오른쪽부터 계산해서 해당 구분자의 오른쪽을 반

      SELECT SUBSTRING_INDEX('www.mariadb.org', '.', -2);
      +---------------------------------------------+
      | SUBSTRING_INDEX('www.mariadb.org', '.', -2) |
      +---------------------------------------------+
      | mariadb.org                                 |
      +---------------------------------------------+