SQLでURLから数字の部分だけ抜き出してくる(/区切りで2箇所ある内の片方から)

Question

SQLで、URLから数字を抜き出してくるクエリを書きたいのですが、数字の箇所が下記例のように2箇所あり、数字だけ抜き出そうとするとどちらも合わせて抜き出されてしまいます。桁数などは決まっていないので、どのように抜き出したらいいのかもわからず、、、
よろしくお願い致します。m(__)m

--https://teratail.com/questions/0002/011/
--右側の方の数字だけ抜き出したい

select regexp_extract(url,'[0-9]+')
--これではだめか…

Comments

[showkit]

SQLで実現するより、文字列で抜き出して、デリミタりよる分割・収集するほうが楽なんですが、そういう実現方法ではだめなんですか？

Answer 1

--https://teratail.com/questions/0002/011/
--右側の方の数字だけ抜き出したい

前提が011/のように/で終了している場合ですが

select replace((regexp_matches(url,'[0-9]+/$'))[1],'/','') from hoge;

数字が2個有る場合の2番目

select (regexp_matches(url,'[0-9]+','g'))[1] from hoge offset 1 limit 1;

select (regexp_matches((regexp_matches(url,'[0-9]+/[0-9]+'))[1],'[0-9]+$'))[1] from hoge;

Answer 2

urlの最後の数値を取り出すという事であれば、以下で取得は可能です。
Postgresの8.4以降

select url_part
from unnest(string_to_array(url,'/')) as w(url_part)
where url_part SIMILAR TO '[0-9]+'
order by row_number() over() desc
limit 1

9.6以降ならunnestのWITH ORDINALITYが使用できるので、以下の様にすることもできます。

select url_part
from unnest(string_to_array(url,'/')) WITH ORDINALITY as w(url_part)
where url_part SIMILAR TO '[0-9]+'
order by ORDINALITY desc
limit 1

何れもサブクエリーとしての使用を想定しています。

2個目限定ということであれば、以下のように。

select url_part from (
        select url_part, row_number() over() as part_index
        from unnest(string_to_array(url,'/')) as w(url_part)
        where url_part SIMILAR TO '[0-9]+'
) tmp
where part_index=2