質問: 下記のようなテーブル 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で後ろから読んで最初の()を拾ってくるという書き方はありませんでしょうか? よろしくお願いいたします。
回答: ``` UPDATE title SET sub_no = SUBSTRING_INDEX(SUBSTRING_INDEX(sub, '(', -1), ')', 1); ``` > > 文字列 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) ``` ちなみにこのご質問は、以前のご質問とほぼ同じ内容です。 もう少し慎重に過去の投稿などを調べてみてください。 > > ()の存在しない場合は、 > > 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関数はマルチバイトセーフではないため、正しい/正しくない を誤って判定する可能性はあります。 > > REGEXP および RLIKE 演算子はバイト単位で機能するため、マルチバイトセーフではなく、マルチバイト文字セットを使用すると想定外の結果が生成される可能性があります。 > > > もし、上の方法でもうまくいかない場合は、スクリプトを組んで1行ずつ ``` 形式のチェック -> UPDATE ``` を繰り返すのが、結局は最も確実な方法だと思います。