declare url_regex default r'https?://(?:[\w-]+\.)+[\w-]+(?:/[\w./?%&=]*)?';
-- URLを捕捉するための正規表現を予め宣言。

-- 1. テーブルごとの捜索対象列を全て縦に並べる。その後、各テーブルをUNION ALLで結合。
with melt_tbl as (
  select
    member_id
    ,'article' as table_name
    ,id
    ,column_name
    ,strings
  from 
    blog_article_tbl
    ,unpivot exclude nulls (
      strings -- 捜索対象データが格納される列名
      for column_name in (title, body) -- 捜索対象データが含まれる列名を、新規テーブルのcolumn_name列に格納
    )
  union all
  select
    member_id
    ,'component' as table_name
    ,id
    ,column_name
    ,strings
  from 
    blog_component_tbl
    ,unpivot exclude nulls (
      strings
      for column_name in (text, link_url)
    )
)
-- 2. melt_tblの行ごとに複数のURLを抽出。
,extract_tbl as (
  select
    member_id
    ,table_name
    ,id
    ,column_name
    ,regexp_extract_all(strings, url_regex) as urls
  from
    melt_tbl
)
-- 3. extract_tblの配列を分解し、1行に1URLが含まれるテーブルを出力。
select
  member_id
  ,table_name
  ,id
  ,column_name
  ,url
from
  extract_tbl
  ,unnest(urls) as url -- 配列をunnest
;