とあるサイトのアクセスログを調査してて、URLの末端を取得する必要が出てきて困った時に対処した方法。
データの準備
https://boxcode.jp/2023/01/test1/
https://boxcode.jp/2023/01/02/test2
https://boxcode.jp/2023/01/02/03/test3/
https://boxcode.jp/2023/01/02/03/04/test4/最終的に
test1
test2
test3
test4
を取得します。
面倒なのでwith句で以下の通り、準備
with url as (
select 'https://boxcode.jp/2023/01/test1/' as url
union all
select 'https://boxcode.jp/2023/01/02/test2' as url
union all
select 'https://boxcode.jp/2023/01/02/03/test/' as url
union all
select 'https://boxcode.jp/2023/01/02/03/04/test/' as url
)抽出方法
以下の方法で取得しました。
with url as (
select 'https://boxcode.jp/2023/01/test1/' as url
union all
select 'https://boxcode.jp/2023/01/02/test2' as url
union all
select 'https://boxcode.jp/2023/01/02/03/test3/' as url
union all
select 'https://boxcode.jp/2023/01/02/03/04/test4/' as url
)
select (string_to_array(rtrim(url,'/'), '/'))[length(rtrim(url,'/')) - length(replace(rtrim(url,'/'),'/',''))+1] as urlend
from url
解説
with url as (
select 'https://boxcode.jp/2023/01/test1/' as url
union all
select 'https://boxcode.jp/2023/01/02/test2' as url
union all
select 'https://boxcode.jp/2023/01/02/03/test3/' as url
union all
select 'https://boxcode.jp/2023/01/02/03/04/test4/' as url
)
select rtrim(url,'/') as "A"
,length(rtrim(url,'/')) as "B"
,length(replace(rtrim(url,'/'),'/','')) as "C"
from url結果
まず上記の画像の通り、部品を一つずつ説明。
A・・・URLの末端の「/」を取り除く
rtrim(url,'/')
https://boxcode.jp/2023/01/test1/
↓
https://boxcode.jp/2023/01/test1B・・・Aの結果のURLの文字数をカウント
length(rtrim(url,'/'))
https://boxcode.jp/2023/01/test1
の結果は
32C・・・「/」を削除したAの文字数のカウント
length(replace(rtrim(url,'/'),'/',''))
https://boxcode.jp/2023/01/test1
↓
https:boxcode.jp202301test1
の結果の文字数のカウントの結果は
27B-C・・・AのURLも文字列の「/」の数をカウント
length(rtrim(url,'/')) - length(replace(rtrim(url,'/'),'/',''))
32 - 27 で
https://boxcode.jp/2023/01/test1
内に「/」は５つあるstring_to_array
最後にstring_to_arrayで「/」括りになっている末端の文字
つまり、B – C + 1番目の文字を取得する
※string_to_arrayはここの記事を参考
以上、かなり面倒な方法でした。