MariaDBのテーブルデータを、特定のキーワードによって分割して検索したい

Question

実現したいこと

Maria DBに下記のようなテーブル、およびデータがあります：

MariaDB [test]> desc testTable;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| col1  | varchar(1024) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

MariaDB [test]> select * from testTable;                                                                                                                                                     
+---------------------------------------------------------------------------------------------------------------------------+
| col1
+---------------------------------------------------------------------------------------------------------------------------+
| 【カテゴリ１】カテゴリ１の情報【A】【B】【カテゴリ２】カテゴリ２の情報【C】【D】【カテゴリ３】カテゴリ３の情報【E】【F】
+---------------------------------------------------------------------------------------------------------------------------+

これを、下記のようにカラムを分けて検索したいと考えております：

カテゴリ１                   カテゴリ２                   カテゴリ３
--------------------------  --------------------------  --------------------------
カテゴリ１の情報【A】【B】    カテゴリ２の情報【C】【D】     カテゴリ３の情報【E】【F】

「【カテゴリX】」ごとに文字列を分割し、それぞれの情報のみ検索したいのですが、そのカテゴリ情報の中に【A】【B】といった別の「【】」使う文字列が含まれます。

「カテゴリ１」など、分割の対象となる文字列は固定で、検索結果に含める必要はありません。

このような条件で、カラムを分けて検索する方法をご存じな方がいらっしゃったら、ぜひその方法をご教示いただけませんでしょうか。

試したこと

「regexp_replace」等を使うのかと考えましたが、分割したい文字列内に「【】」がさらに含まれているなどして、正規表現の条件指定が見出せない状態でいます。

補足情報（FW/ツールのバージョンなど）

MariaDB 10.6.5

Answer 1

常識的にはこんなデータのもたせ方はしないのが１番ですが、どうしてもというのであればこんな感じでどうでしょう？

create table tbl(col1 text,col2 json as (concat('{"dummy":"',regexp_replace(col1,'【(カテゴリ.)】','","$1":"'),'"}')));
insert into tbl(col1) values
('【カテゴリ１】カテゴリ１の情報【A】【B】【カテゴリ２】カテゴリ２の情報【C】【D】【カテゴリ３】カテゴリ３の情報【E】【F】'),
('【カテゴリ１】カテゴリ１の情報【G】【カテゴリ３】カテゴリ３の情報【H】'),
('【カテゴリ１】カテゴリ１の情報【I】【J】【K】【カテゴリ２】カテゴリ２の情報【L】【M】');

select  
json_value(col2,'$."カテゴリ１"') as `カテゴリ１`,
json_value(col2,'$."カテゴリ２"') as `カテゴリ２`,
json_value(col2,'$."カテゴリ３"') as `カテゴリ３`
 FROM tbl

Comments

[tetakaha759]

ご回答、ありがとうございます。

残念ながら、データの構造はパッケージソフトを使っている関係上、自分ではどうにもできない状態なのです。。

何でもかんでも1つのカラムに詰め込んでいて、ほとほと困っていました。

Answer 2

• 【カテゴリ１】と【カテゴリ２】に囲まれた文字列
• 【カテゴリ２】と【カテゴリ３】に囲まれた文字列
• 【カテゴリ３】から最後までの文字列

この3つを取得したいという認識で正しいでしょうか? であれば以下のようなクエリになるかと思います。

SELECT
  SUBSTRING(col1, INSTR(col1,'【カテゴリ１】')+CHAR_LENGTH('【カテゴリ１】'), INSTR(col1,'【カテゴリ２】')-INSTR(col1,'【カテゴリ１】')-CHAR_LENGTH('【カテゴリ１】')),
  SUBSTRING(col1, INSTR(col1,'【カテゴリ２】')+CHAR_LENGTH('【カテゴリ２】'), INSTR(col1,'【カテゴリ３】')-INSTR(col1,'【カテゴリ２】')-CHAR_LENGTH('【カテゴリ２】')),
  SUBSTRING(col1, INSTR(col1,'【カテゴリ３】')+CHAR_LENGTH('【カテゴリ３】'))
FROM testTable;

Comments

[tetakaha759]

ご回答、ありがとうございました！

まさに、私がやりたかったことをご回答いただいたので、ベストアンサーとさせて頂きます。