SQLでURLの部分的切り出しを行う
背景
DB内にURLを格納しており、取り出してから文字列操作を行わず、SQLで対応する
対象DB
RedshiftとPostgreSQLで動作確認済
※ RedshiftはPostgreSQLをベースにしている様です。
切り出し
プロトコル部分をなくす
Redshift
LTRIM(LTRIM(url, 'http://'), 'https://')
[2015/03/02追記]
上記の内容では、domain部分の一部まで切り取られてしまいます。
なぜなら、LTRIMは指定した文字列が連続する限り取り除くからです。
なので、正しくは以下の内容です。
Redshift
LTRIM(LTRIM(url, 'htps'), ':/')
ドメイン部分切り出し
Redshift
REGEXP_SUBSTR(url, '(http://|https://)[^/]*')
-
httpとhttpsの2つのプロトコルをまとめて取り除く -
[^/]*でプロトコル以降、初めての/部分までを切り出す
クエリパラメータを削除する
Redshift
SPLIT_PART(url, '?', 1)
- 1がマッチした部分をとりだし、ここでは、
?より前