SQLite3でURLからサイト/ドメイン名を抽出する方法

2024-05-26

SQLite3でURLからサイト/ドメイン名を取得する

手順

正規表現でURLを解析
サブクエリで結果を抽出

例

-- サンプルデータ
CREATE TABLE urls (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  url TEXT NOT NULL
);

INSERT INTO urls (url) VALUES
  ('https://www.example.com'),
  ('https://www.google.com/search?q=sqlite'),
  ('https://stackoverflow.com/questions/tagged/sqlite');

-- サイト/ドメイン名の抽出
SELECT url,
       REGEXP(url, '^(?:https?:\/\/)?([^/]+)') AS domain
FROM urls;

解説

上記の例では、urlsというテーブルにURLを格納しています。
REGEXP関数を使用して、URLからホスト名部分 (サイト/ドメイン名) を抽出しています。
SELECTステートメントとWHERE句を使用して、抽出されたホスト名部分を基に目的のデータを取得しています。
- この例では、抽出されたホスト名 (domain) と元のURL (url) を両方とも表示しています。

補足

上記は基本的な例であり、抽出する情報や条件に応じて正規表現やサブクエリを調整する必要があります。
より高度な解析には、SUBSTR関数やREPLACE関数などの他の組み込み関数も使用できます。

サンプルコード：URLからサイト/ドメイン名を抽出する

データベースの作成とデータの挿入

CREATE TABLE urls (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  url TEXT NOT NULL
);

INSERT INTO urls (url) VALUES
  ('https://www.example.com'),
  ('https://www.google.com/search?q=sqlite'),
  ('https://stackoverflow.com/questions/tagged/sqlite');

サイト/ドメイン名の抽出

SELECT url,
       REGEXP(url, '^(?:https?:\/\/)?([^/]+)') AS domain
FROM urls;

結果

url	domain
https://www.example.com	www.example.com
https://www.google.com/search?q=sqlite	www.google.com
https://stackoverflow.com/questions/tagged/sqlite	stackoverflow.com

SQLite3でURLからサイト/ドメイン名を取得するその他の方法

SELECT url,
       substr(url, instr(url, '://') + 3, length(url) - instr(url, '/')) AS domain
FROM urls;

instr()関数を使用して、URL中に "://" が最初に現れる位置を特定します。
substr()関数を使用して、その位置からスラッシュ (/) が最初に現れる位置までの部分 (ホスト名) を切り取ります。
length()関数を使用して、URLの長さを取得します。

replace()関数を使用する

SELECT url,
       replace(replace(url, 'https://', ''), 'http://', '') AS domain
FROM urls;

replace()関数を使用して、URL中の "https://" と "http://" を空文字に置き換えます。
これにより、ホスト名部分が抽出されます。

正規表現とCASE式を使用する

SELECT url,
       CASE
         WHEN instr(url, '://') > 0 THEN
           REGEXP(url, '^(?:https?:\/\/)?([^/]+)')
         ELSE
           url
       END AS domain
FROM urls;

CASE式を使用して、URL中に "://" が存在するかどうかを確認します。
存在する場合は、正規表現を使用してホスト名部分を抽出します。
存在しない場合は、URL全体をそのままホスト名として扱います。

サブクエリを使用する

SELECT url,
       (SELECT substr(t.url, instr(t.url, '://') + 3, length(t.url) - instr(t.url, '/'))
        FROM urls AS t
        WHERE t.id = u.id) AS domain
FROM urls AS u;

サブクエリを使用して、同じ行のURLからホスト名部分を抽出します。
WHERE句を使用して、サブクエリと主クエリを関連付けます。

それぞれの方法の利点と欠点

方法	利点	欠点
`substr()`と`length()`	シンプルで分かりやすい	プロトコル ("https://" または "http://") が存在しないURLには対応できない
`replace()`	シンプルで分かりやすい	プロトコル ("https://" または "http://") が存在しないURLには対応できない
正規表現と`CASE`式	プロトコル ("https://" または "http://") が存在しないURLにも対応できる	正規表現の書き方が複雑になる可能性がある
サブクエリ	プロトコル ("https://" または "http://") が存在しないURLにも対応できる	サブクエリを使用するため、クエリが複雑になる