[PostgreSQL] 문자열 다루기(추출/ 분해 / 합치기) (substring, split_part, concat)

이번 포스팅은 데이터에서 특정 요소들만 추출하는 작업을 해보겠습니다.

데이터셋입니다.

DROP TABLE IF EXISTS access_log ;
CREATE TABLE access_log (
    stamp    varchar(255)
  , referrer text
  , url      text
);

INSERT INTO access_log 
VALUES
    ('2020-05-21 12:02:00', 'http://www.naver.com/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.kakao.com/video/detail?id=001')
  , ('2020-05-21 12:02:01', 'http://www.daum.net/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.kakao.com/video#ref'          )
  , ('2020-05-21 12:02:02', 'https://www.naver.com/'                               , 'http://www.kakao.com/book/detail?id=002' )
  , ('2020-05-21 12:02:03', 'http://www.daum.net/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.kakao.com/webtoon#ref'          )
  , ('2020-05-21 12:02:03', 'http://www.daum.net/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.kakao.com/news#ref?id=003'          )

;

 

문자열 추출(Substring)

여기서 앞의 https://와 뒤에 붙은 상세 주소를 빼고 www.naver.com  같은 메인 주소만 가지고 와보겠습니다.

이를 위해서는 문자열을 잘라야 하는데 이때쓰이는 함수가 substring이며 해당 함수의 문법은 다음과 같습니다.

substring(string [from <추출이 시작되는 곳>] [for <추출글자 개수>])

 

PostgreSQL에서는 정규표현식을 쓸 수 있습니다. 따라서 substring와 정규표현식을 활용하여 메인주소를 추출하겠습니다.

Select stamp
	,substring(referrer from 'https?://([^/]*)') as referrer_host
From access_log;

 

이번에는 사이트 URL에서 id와 path를 추출해보겠습니다.

select stamp, url
	,substring(url from '//[^/]+([^?#]+)') as path
	,substring(url from 'id=([^&]*)') as id
from access_log;

 

문자열 분해(Split_part)

다음은 문자열을 분해해보겠습니다.

문자열을 분해할때 쓰는 함수는 다음과 같습니다.

split_part('문자열', '자를문자', 위치)

해당 함수를 이용하여 URL을 '/'기준으로 잘라보겠습니다.

select stamp, url
	,split_part(substring(url from '//[^/]+([^?#]+)'),'/',2) as path1
	,split_part(substring(url from '//[^/]+([^?#]+)'),'/', 3) as path2
from access_log;

 

문자열 합치기(concat)

문자열을 합치는 concat의 문법은 다음과 같습니다.

concat(앞문자열, "중간삽입글자(옵션)", 뒷문자열)

CONCAT를 활용하여 stamp와 url을  '-'를 활용하여 붙여보겠습니다.

select stamp, url
	,concat(stamp, ' - ', url) as stamp_url
from access_log;