mysql8で追加された正規表現の関数を勉強してみた

私はmysql初心者です。間違いなどありましたら、教えていただけるとありがたいです。

参考
https://dev.mysql.com/doc/refman/8.0/en/regexp.html

これらが8で追加されました

- REGEXP_LIKE()
- REGEXP_SUBSTR()
- REGEXP_REPLACE()
- REGEXP_INSTR()

# `REGEXP_LIKE(expr, pat[, match_type])`

- 文字列`expr`が`pat`で指定された正規表現と一致すると`1`を返し、一致していなかったら`0`を返す
- `expr` または `pat` が NULL の場合、戻り値は NULL 

### `match_type`

- `c`: 大文字小文字を区別する
- `i`: 大文字小文字を区別しない
- `m`: 複数行モード、デフォルトだと文字列の最初と最後のみが`^`や`$`などに一致するが、これを指定すると`\n`の前後も行の最初と最後として扱ってくれる
- `n`: 行の終端に`.`がマッチする
- `u`: unixの行末のみにする、`\n`は行末として扱われて`\r\n`は扱われないようす

矛盾するオプションは右側が優先される。`ci`だったら`i`が有効になる。

```sql
select
  regexp_like('abc', '[a-z]{3}') 一致
  ,regexp_like('ab', '[a-z]{3}') 不一致;
```

![Screen Shot 2020-06-22 at 0.50.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/96b934b0-534f-b5dd-8b25-5f30aab45d6c.png)

```sql
select
  regexp_like('ABC', '[a-z]{3}', 'c') c大文字小文字区別する
  ,regexp_like('ABC', '[a-z]{3}', 'i') i大文字小文字区別しない
  ,regexp_like('aaa\nbbb', 'a$') mなし
  ,regexp_like('aaa\nbbb', '^b') mなし
  ,regexp_like('aaa\nbbb', 'a$', 'm') mあり
  ,regexp_like('aaa\nbbb', '^b', 'm') mあり
  ,regexp_like('a\nb', '...') nなし
  ,regexp_like('a\nb', '...', 'n') nあり
  ,regexp_like('a\r\nb', 'a$', 'm') uなしCRLF
  ,regexp_like('a\r\nb', 'a$', 'mu') uありCRLF
  ,regexp_like('a\nb', 'a$', 'm') uなしLF
  ,regexp_like('a\nb', 'a$', 'mu') uありLF;
```

![Screen Shot 2020-06-22 at 0.59.30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/590b21eb-acb0-866e-f8cb-9eb943227bef.png)

# `REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])`

- 文字列`expr`が`pat`で指定された部分文字列を返す
- `expr` または `pat` が NULL の場合、戻り値は NULL 
- `pos`: 検索開始位置。デフォルトは`1`
- `occurrence`: 例えば3を指定したら3回目に出現した部分。デフォルトは`1`
- `match_type`: REGEXP_LIKEの説明と同じ

```sql
select 
  regexp_substr('abc def ghi', '[a-z]+') デフォルト
  ,regexp_substr('abc def ghi', '[a-z]+', 1) 1文字目から検索
  ,regexp_substr('abc def ghi', '[a-z]+', 5) 5文字目から検索
  ,regexp_substr('abc def ghi', '[a-z]+', 9) 9文字目から検索
  ,regexp_substr('abc def ghi', '[a-z]+', 1, 1) 1回目に出現
  ,regexp_substr('abc def ghi', '[a-z]+', 1, 2) 2回目に出現
  ,regexp_substr('abc def ghi', '[a-z]+', 1, 3) 3回目に出現
```

![Screen Shot 2020-06-22 at 1.07.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/88804ca6-cd56-7eb1-165e-d25e2fcf3f71.png)

# `REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])`

- `expr`の中で、`pat`で指定された正規表現に一致する文字列を置換文字列 `repl` で置換し、その結果の文字列を返す
- `expr` または `pat` または `repl` が NULL の場合、戻り値は NULL 
- pos: T検索開始位置。デフォルトは`1`
- occurrence: 例えば3を指定したら3回目に出現した部分のみ置換。デフォルトは`0`(0を指定すると一致する文字全て置換)
- match_type: REGEXP_LIKEの説明と同じ

```sql
select 
  regexp_replace('abc def ghi', '[a-z]+', 'xxx') デフォルト
  ,regexp_replace('abc def ghi', '[a-z]+', 'xxx', 1) 1文字目から検索
  ,regexp_replace('abc def ghi', '[a-z]+', 'xxx', 5) 5文字目から検索
  ,regexp_replace('abc def ghi', '[a-z]+', 'xxx', 9) 9文字目から検索
  ,regexp_replace('abc def ghi', '[a-z]+', 'xxx', 1, 0) 0は全て置換
  ,regexp_replace('abc def ghi', '[a-z]+', 'xxx', 1, 1) 1回目を置換
  ,regexp_replace('abc def ghi', '[a-z]+', 'xxx', 1, 2) 2回目を置換
  ,regexp_replace('abc def ghi', '[a-z]+', 'xxx', 1, 3) 3回目を置換
```

![Screen Shot 2020-06-22 at 1.14.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/00f87764-1e9e-df3c-04bd-9d2c5476c17b.png)

# `REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])`

- `pat`で指定された正規表現に一致する`expr`の開始インデックスを返す
- `expr` または `pat` が NULL の場合、戻り値は NULL 
- 文字インデックスは 1 から
- `pos`: 検索開始位置。デフォルトは`1`
- `occurrence`: 例えば3を指定したら3回目に出現した部分。デフォルトは`1`
- `return_option`: 0を指定した場合、ッチした部分文字列の最初の文字の位置を返す。1を指定した場合、マッチした部分文字列の後の位置を返す。省略した場合、デフォルトは`0`

```sql
select 
  regexp_instr('abc def ghi', '[a-z]{3}') デフォルト
  ,regexp_instr('abc def ghi', '[a-z]{3}', 1) 1文字目から
  ,regexp_instr('abc def ghi', '[a-z]{3}', 2) 5文字目から
  ,regexp_instr('abc def ghi', '[a-z]{3}', 7) 9文字目から
  ,regexp_instr('abc def ghi', '[a-z]{3}', 1, 1) 1回目に出現
  ,regexp_instr('abc def ghi', '[a-z]{3}', 1, 2) 2回目に出現
  ,regexp_instr('abc def ghi', '[a-z]{3}', 1, 3) 3回目に出現
  ,regexp_instr('abc def ghi', '[a-z]{3}', 1, 1, 0) 一致した位置
  ,regexp_instr('abc def ghi', '[a-z]{3}', 1, 1, 1) 一致した後の位置
```

![Screen Shot 2020-06-22 at 1.23.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/d53900a4-c36f-6433-6156-cbb0e2d519de.png)

見ていただいてありがとうございましたm(_ _)m