正規表現を用いて格納

Question

実現したいこと

複数のパラメータを持つデータを各カラムに格納したい

前提

１つのカラムに
12日/18日/1月12日/3月20日/水曜日/金曜日/第一水曜日/第三日曜日のようなデータが入っており、
似たようなカラムが多数あります。
このデータを、ｎ日系/ｎ月ｎ日系/曜日系/第ｎ〇曜日系　のように分けて、横持ちで格納したいです。

[元データ]
event_date
9日/12月10日
9日/水曜日
9日/10月10日/10月11日
第2土曜日/第4土曜日/9日/19日
9日
8日/第4土曜日
8日
8日/12日
8日/12日
8日/12日
8日/12日
8日
8日/16日
8日
8日
8日/土曜日/日曜日
7日/11日/15日/22日/金曜日
7日/11日/22日
7日/第2水曜日
7日
7日/第3日曜日
7日
7日/22日/24日
7日/15日/金曜日
7日/16日/27日

【完成イメージ】

[イメージ]
カラム①(1つ目のｎ日)：12日　カラム②(2つ目のｎ日)：18日　カラム③(1つ目の〇曜日)：水曜日　カラム④(2つ目の〇曜日)：金曜日　カラム⑤(１つの第ｎ〇曜日)：第一水曜日　カラム⑥(2つ目の第ｎ〇曜日)：第三日曜日

該当のソースコード

### 試したこと
SELECT 
  -- 毎日～日をday_に格納
  CASE 
    WHEN event_date NOT LIKE '%[0-9]月%' AND event_date NOT LIKE '%[0-9]月[0-9]+日%' THEN REGEXP_SUBSTR(event_date, '([0-9]+日)')
    ELSE NULL
  END AS day_1,
  CASE 
    WHEN event_date NOT LIKE '%[0-9]月%' AND event_date NOT LIKE '%[0-9]月[0-9]+日%'  THEN REGEXP_SUBSTR(event_date, '([0-9]+日)', 1, 2)
    ELSE NULL
  END AS day_2,
  -- 特定の日付(1月23日等)をmonth_day_に格納
   REGEXP_SUBSTR(event_date, '[0-9]+月[0-9]+日') AS month_day,
   REGEXP_SUBSTR(event_date, '[0-9]+月[0-9]+日',1 ,2) AS month_day_2,
   -- 曜日をday_week_に格納
   CASE 
     WHEN event_date NOT LIKE '%第%'  THEN REGEXP_SUBSTR(event_date, '([月火水木金土日]曜日)')
     ELSE NULL
   END AS day_week_1,
   CASE 
     WHEN event_date NOT LIKE '%第%'  THEN REGEXP_SUBSTR(event_date, '([月火水木金土日]曜日)',1 ,2)
     ELSE NULL
   END AS day_week_2,
    -- 特定の曜日(第ｎ〇曜日/を最終〇曜日)をsp_day_week_に格納
   REGEXP_SUBSTR(event_date, '([第]+[12345一二三四五]+[月火水木金土日]+曜日)') AS sp_day_week_1,
   REGEXP_SUBSTR(event_date, '([第]+[12345一二三四五]+[月火水木金土日]+曜日)',1 ,2) AS sp_day_week_2
FROM A1

わからないこと

上記クエリではｘ月ｙ日のデータについて、ｙ日の部分がday_にも格納されてしまいます。
day_にはｘ月ｙ日のような月日のデータは格納せずに、z日のような日付のみのデータが格納されるようにしたいです。また、ｘ月ｙ日のような月日のデータはmonth_dayに格納したいです。。

初級者のため、前提が間違っているかもしれないので、その際にはコメントをお願い致します。

Comments

[yambejp]

テーブルのサンプルとほしい結果を例示ください

[yambejp]

サンプルデータは画像と同等のものをテキストでも付加してください

Answer 1

適当ですが・・・

create table tbl(id int primary key auto_increment,
event_date text
);

insert into tbl(event_date) values
('9日/12月10日'),
('9日/水曜日'),
('9日/10月10日/10月11日'),
('第2土曜日/第4土曜日/9日/19日'),
('9日'),
('8日/第4土曜日'),
('8日'),
('8日/12日'),
('8日/12日'),
('8日/12日'),
('8日/12日'),
('8日'),
('8日/16日'),
('8日'),
('8日'),
('8日/土曜日/日曜日'),
('7日/11日/15日/22日/金曜日'),
('7日/11日/22日'),
('7日/第2水曜日'),
('7日'),
('7日/第3日曜日'),
('7日'),
('7日/22日/24日'),
('7日/15日/金曜日'),
('7日/16日/27日');


SELECT id,event_date,
regexp_substr(event_date,'[0-9]+日') as day1,
regexp_substr(event_date,'[0-9]+日',1,2) as day2,
regexp_substr(event_date,'[0-9]+日',1,3) as day3,
regexp_substr(event_date,'[0-9]+日',1,4) as day4,
regexp_substr(event_date,'[0-9]+月[0-9]+日') as month1,
regexp_substr(event_date,'[0-9]+月[0-9]+日',1,2) as month2,
substr(regexp_substr(event_date,'/.曜日'),2) as week1,
substr(regexp_substr(event_date,'/.曜日',1,2),2) as week2,
regexp_substr(event_date,'第[0-9].曜日') as sp1,
regexp_substr(event_date,'第[0-9].曜日',1,2)  as sp2
FROM tbl

調整版

SELECT id,event_date,
coalesce(regexp_substr(event_date,'^[0-9]+日|(!月)[0-9]+日'),substr(regexp_substr(event_date,'/[0-9]+日'),2)) as day1,
substr(regexp_substr(event_date,'(^|/)[0-9]+日',1,2),2) as day2,
substr(regexp_substr(event_date,'(^|/)[0-9]+日',1,3),2) as day3,
substr(regexp_substr(event_date,'(^|/)[0-9]+日',1,4),2) as day4,
regexp_substr(event_date,'[0-9]+月[0-9]+日') as month1,
regexp_substr(event_date,'[0-9]+月[0-9]+日',1,2) as month2,
substr(regexp_substr(event_date,'/.曜日'),2) as week1,
substr(regexp_substr(event_date,'/.曜日',1,2),2) as week2,
regexp_substr(event_date,'第[0-9].曜日') as sp1,
regexp_substr(event_date,'第[0-9].曜日',1,2)  as sp2
FROM tbl

Comments

[zelka]

ご回答ありがとうございます！
曜日の方はうまくいったのですが、日付の方がまだ出来なかったです。
('9日/10月10日/10月11日')の場合、day1に9日、day2~４はnull、month1に10月10日、month2に10月11日が入るようにするのは可能でしょうか・・・？
頂いたクエリだと('9日/10月10日/10月11日')の際にday2に10日、day3に11日が入ったのですが、入らないようにしたいです。

[yambejp]

調整版あげました。どうでしょう？

[zelka]

問題ありませんでした！
誠にありがとうございました！

[zelka]

誠に申し訳ございません。。。
1日/7日/15日/25日のようなデータの場合、なぜか7日しかDAY1に格納されず、DAY2以降が空カラムになってしまいます。。。
ｎ日が複数あるとおこってしまうのですが、解消方法はありますでしょうか？

＃追記
先頭にあるデータが格納されていないみたいです・・・
木曜日/25日みたいなデータだと25日は正しく格納されますが、木曜日が格納されないです。。。

Answer 2

やりかたはいろいろあると思いますが、REPLACE関数を使ってREGEXP_SUBSTRの結果から「日」という字を削除するのはどうでしょうか。

 REPLACE(REGEXP_SUBSTR(event_date, '([0-9]+日)'), '日', '')

---

2023-10-17 追記

質問を誤解していたようです。申し訳ありません。たとえば以下のようなSQLはどうでしょうか?

SELECT 
  event_date,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[0-9]+日', 1, 1), 2) AS day_1,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[0-9]+日', 1, 2), 2) AS day_2,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[0-9]+日', 1, 3), 2) AS day_3,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[0-9]+日', 1, 4), 2) AS day_4,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[0-9]+月[0-9]+日', 1, 1), 2) AS month_day_1,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[0-9]+月[0-9]+日', 1, 2), 2) AS month_day_2,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[月火水木金土日]曜日', 1, 1), 2) AS day_week_1,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[月火水木金土日]曜日', 1, 2), 2) AS day_week_2,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[第]+[12345一二三四五]+[月火水木金土日]+曜日', 1, 1), 2) AS sp_day_week_1,
  SUBSTR(REGEXP_SUBSTR(CONCAT('/', event_date), '/[第]+[12345一二三四五]+[月火水木金土日]+曜日', 1, 2), 2) AS sp_day_week_2
FROM A1

処理のイメージは以下の通りです。

1. 7日/11日/15日/22日/金曜日というデータを/7日/11日/15日/22日/金曜日に変換する
2. 変化したデータから正規表現を利用して/7日や/金曜日というデータを取り出す
3. 取り出したデータの先頭にある/を削除する

Comments

[zelka]

日を消すと曜日の「日」も消えて微妙だったので難しそうです・・・