【MySQL】後ろから読んで最初の()を拾ってくるという書き方

Question

下記のようなテーブル title があります。
カラム(div,sub,sub_no)

div sub sub_no
1000 カローラ販売(1)
1001 セリカ販売(31)
1002 ブルーバード販売(14)
1003 コロナ販売(15)

上記の末尾にある()内の数字を sub_no に書き写すMySQLを組みました。

UPDATE `title`,(SELECT `div`, `sub`, TRIM(SUBSTRING(`sub`, LOCATE('(',`sub`)+1, LOCATE(')',`sub`)- LOCATE('(',`sub`)-1)) AS `ABC` FROM `title` ) as AAA SET `title`.`sub_no`= `AAA`.`ABC` WHERE `title`.`div`= `AAA`.`div`;

上記では想定通り下記のようになります。

div sub sub_no
1000 カローラ販売(1) 1
1001 セリカ販売(31) 31
1002 ブルーバード販売(14) 14
1003 コロナ販売(15) 15

上記の場合は問題なかったのですが、下記だと問題が生じました。

テーブル title
div sub sub_no
1000 カローラ(東京)販売(1)
1001 セリカ販売(31)
1002 ブルーバード(多摩)販売(14)
1003 コロナ販売(15)

上記の場合、本来であれば「カローラ(東京)販売(1)」では1を表示してほしいのですが、先頭から()を探しに行くらしく最初に出てくる東京が表示されてしまいます。
ブルーバード(多摩)販売(14) も同様に多摩が表示されます。

div sub sub_no
1000 カローラ(東京)販売(1) 東京
1001 セリカ販売(31) 31
1002 ブルーバード(多摩)販売(14) 多摩
1003 コロナ販売(15) 15

質問です。
MySQLで後ろから読んで最初の()を拾ってくるという書き方はありませんでしょうか?
よろしくお願いいたします。

Answer 1

UPDATE title SET sub_no = SUBSTRING_INDEX(SUBSTRING_INDEX(sub, '(', -1), ')', 1);

https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_substring-index

文字列 str から、区切り文字 delim が count 回出現する前の部分文字列を返します。count が正の値の場合は、(左から数えて) 最後の区切り文字の左側にあるすべてが返されます。count が負の値の場合は、(右から数えて) 最後の区切り文字の右側にあるすべてが返されます。

動作結果

mysql> CREATE TABLE title (
    ->   `div` INT,
    ->   sub VARCHAR(16),
    ->   sub_no INT
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO title (`div`, sub) VALUES
    -> (1000, 'カローラ(東京)販売(1)'),
    -> (1001, 'セリカ販売(31)'),
    -> (1002, 'ブルーバード(多摩)販売(14)'),
    -> (1003, 'コロナ販売(15)');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM title;
+------+--------------------------------------+--------+
| div  | sub                                  | sub_no |
+------+--------------------------------------+--------+
| 1000 | カローラ(東京)販売(1)                |   NULL |
| 1001 | セリカ販売(31)                       |   NULL |
| 1002 | ブルーバード(多摩)販売(14)           |   NULL |
| 1003 | コロナ販売(15)                       |   NULL |
+------+--------------------------------------+--------+
4 rows in set (0.00 sec)

mysql> UPDATE title SET sub_no = SUBSTRING_INDEX(SUBSTRING_INDEX(sub, '(', -1), ')', 1);
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM title;
+------+--------------------------------------+--------+
| div  | sub                                  | sub_no |
+------+--------------------------------------+--------+
| 1000 | カローラ(東京)販売(1)                |      1 |
| 1001 | セリカ販売(31)                       |     31 |
| 1002 | ブルーバード(多摩)販売(14)           |     14 |
| 1003 | コロナ販売(15)                       |     15 |
+------+--------------------------------------+--------+
4 rows in set (0.00 sec)

---

ちなみにこのご質問は、以前のご質問とほぼ同じ内容です。
https://teratail.com/questions/78649

もう少し慎重に過去の投稿などを調べてみてください。

---

()の存在しない場合は、

div sub sub_no
1003 クラウン販売 　
のようにsub_noをNULLにしたいと考えています。

「()の存在しない場合」の他にも「正しくない形式」のパターンを挙げだすと、キリがありません。
例えば

• ほげ（5a）のように、() の中に数字以外の文字が混ざっている
• ほげ 10)のように、カッコの開閉が対応していない

など。

なので、以下のように「正しい形式」のレコードと「正しくない形式」のレコードを別々に更新するのが良いと思います。

「正しい形式」に対する UPDATE文

UPDATE title
SET sub_no = REPLACE(SUBSTRING_INDEX(sub, '(', -1), ')', '')
WHERE sub REGEXP '.*\\([0-9]+\\)$';

「正しくない形式」に対する UPDATE文

UPDATE title
SET sub_no = NULL
WHERE sub NOT REGEXP '.*\\([0-9]+\\)$';

---

ただし、MySQL の REGEXP関数はマルチバイトセーフではないため、正しい/正しくない を誤って判定する可能性はあります。
https://dev.mysql.com/doc/refman/5.6/ja/regexp.html

REGEXP および RLIKE 演算子はバイト単位で機能するため、マルチバイトセーフではなく、マルチバイト文字セットを使用すると想定外の結果が生成される可能性があります。

もし、上の方法でもうまくいかない場合は、スクリプトを組んで1行ずつ

形式のチェック -> UPDATE

を繰り返すのが、結局は最も確実な方法だと思います。

Comments

[tajix_japan]

有難うございます。
SELECT REPLACE(SUBSTRING_INDEX(sub, '(', -1), ')', '') AS number FROM `table`;

で組んでいたのですが、下記の悪さをすることがわかりました。

div sub sub_no
1000 カローラ(東京)販売(1) 1
1001 セリカ販売(31) 31
1002 ブルーバード(多摩)販売(14) 14
1003 コロナ販売(15) 15

上記まではできます。

しかし、

1003 クラウン販売

のように()が存在しない例があった場合に、
下記のようにsubの全文を取ってきてしまうことが判明しました。
div sub sub_no
1003 クラウン販売 　クラウン販売

()の存在しない場合は、
div sub sub_no
1003 クラウン販売 　
のようにsub_noをNULLにしたいと考えています。

そのため、上記のSUBSTRING_INDEXを廃止し、新しい方法を考えているところです。

もちろん、SUBSTRING_INDEXを使って、()がなけれはNULLを返すという方法でも
構いません。

ご助言いただけましたら幸いです。

よろしくお願いいたします。

[KiyoshiMotoki]

回答に追記しました。
ご確認ください。

[tajix_japan]

有難うございます。
「正しい形式」のレコードと「正しくない形式」のレコードを別々に更新することで対応できました。
助かりました。深く御礼申し上げます。