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

実現したいこと

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

回答2件

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

SQL
1create table tbl(col1 text,col2 json as (concat('{"dummy":"',regexp_replace(col1,'【(カテゴリ.)】','","$1":"'),'"}')));
2insert into tbl(col1) values
3('【カテゴリ１】カテゴリ１の情報【A】【B】【カテゴリ２】カテゴリ２の情報【C】【D】【カテゴリ３】カテゴリ３の情報【E】【F】'),
4('【カテゴリ１】カテゴリ１の情報【G】【カテゴリ３】カテゴリ３の情報【H】'),
5('【カテゴリ１】カテゴリ１の情報【I】【J】【K】【カテゴリ２】カテゴリ２の情報【L】【M】');
6
7select  
8json_value(col2,'$."カテゴリ１"') as `カテゴリ１`,
9json_value(col2,'$."カテゴリ２"') as `カテゴリ２`,
10json_value(col2,'$."カテゴリ３"') as `カテゴリ３`
11 FROM tbl

投稿2023/06/09 10:25

yambejp

総合スコア112600

回答へのコメント

tetakaha759

2023/06/09 13:28

ご回答、ありがとうございます。残念ながら、データの構造はパッケージソフトを使っている関係上、自分ではどうにもできない状態なのです。。何でもかんでも1つのカラムに詰め込んでいて、ほとほと困っていました。

ベストアンサー

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

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

SQL
1SELECT
2  SUBSTRING(col1, INSTR(col1,'【カテゴリ１】')+CHAR_LENGTH('【カテゴリ１】'), INSTR(col1,'【カテゴリ２】')-INSTR(col1,'【カテゴリ１】')-CHAR_LENGTH('【カテゴリ１】')),
3  SUBSTRING(col1, INSTR(col1,'【カテゴリ２】')+CHAR_LENGTH('【カテゴリ２】'), INSTR(col1,'【カテゴリ３】')-INSTR(col1,'【カテゴリ２】')-CHAR_LENGTH('【カテゴリ２】')),
4  SUBSTRING(col1, INSTR(col1,'【カテゴリ３】')+CHAR_LENGTH('【カテゴリ３】'))
5FROM testTable;