MySQLでヌル文字(NUL)などの制御文字が含まれている文字列を探す方法のメモ

ヌル文字(NUL) の判定

MySQLではnull文字は '\0' や CHAR(0) で表せます

なので ヌル文字を含む文字列は 文字列 LIKE '%\0%' のような感じで判定できます(他の文字列系の関数でもよいはず)

mysql> SELECT CHAR(0) LIKE '%\0%';
+---------------------+
| CHAR(0) LIKE '%\0%' |
+---------------------+
|                   1 |
+---------------------+

その他の制御文字の判定

単に制御文字が含まれるかどうかなら、正規表現を使って 文字列 REGEXP '[[:cntrl:]]'でよさそう
ただし、注意点としては試した環境(MySQL 5.7)では正規表現だとヌル文字の判定はできませんでした(正規表現ライブラリの関係？)

制御文字の特定の範囲を指定したい場合

CHAR(文字の番号)

力技で行くと、CHAR(文字の番号)を使って正規表現で文字列の範囲の[]を文字列結合で作ります

mysql> SELECT CHAR(10) REGEXP CONCAT('[', CHAR(1), '-', CHAR(20), ']');
+----------------------------------------------------------+
| CHAR(10) REGEXP CONCAT('[', CHAR(1), '-', CHAR(20), ']') |
+----------------------------------------------------------+
|                                                        1 |
+----------------------------------------------------------+

[.characters.]

または[.characters.] という構文(charactersは文字の名前)がMySQLの正規表現では使えるらしいのでこういう風にも書けます
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5.2 正規表現

SELECT CHAR(10) REGEXP '[[.NUL.]-[.DLE.]]';

直接制御文字を入力

使っているシェルで直接制御文字を入力できるならそのまま書けます
たとえばbashだと以下の記事のようにCtrl+Vの後に打ちたい制御文字に対応したキー、例えばCtrl+Aを打つと直接制御文字が入力できます(この場合は表示上^Aが入力されます)
qiita.com

これを使って正規表現の範囲を指定できます

SELECT CHAR(10) REGEXP '[^A-^P]';

BigQueryのSTRUCT型やARRAY型で無理やりGROUP BYで集計する

BigQueryではSTRUCT(構造体)型やARRAY(配列)型が使えます
データ型  |  BigQuery  |  Google Cloud

STRUCT型のカラムでGROUP BYしようとするとGrouping by expressions of type STRUCT is not allowed atのようなエラーが出てきて実行することができません

同様にARRAY型のカラムだとGrouping by expressions of type ARRAY is not allowed atのようになります

これをどうにかしてGROUP BYすることを考えます

方法1: 構造体の中身を一度展開してGROUP BYする

構造体の場合、展開して新しいSTRUCTに入れ直して、展開したそれぞれのカラムでGROUP BYすると実行できます

SELECT
  STRUCT(s.x, s.y) AS s,
  COUNT(*) AS c
FROM (
  SELECT STRUCT(1 AS x, 1 AS y) AS s
  UNION ALL
  SELECT STRUCT(2 AS x, 2 AS y) AS s
  UNION ALL
  SELECT STRUCT(1 AS x, 1 AS y) AS s
) AS src
GROUP BY src.s.x, src.s.y

もしくはANY_VALUE()を使っても書けます

SELECT
  ANY_VALUE(s) AS s,
  COUNT(*) AS c
FROM (
  SELECT STRUCT(1 AS x, 1 AS y) AS s
  UNION ALL
  SELECT STRUCT(2 AS x, 2 AS y) AS s
  UNION ALL
  SELECT STRUCT(1 AS x, 1 AS y) AS s
) AS src
GROUP BY src.s.x, src.s.y

配列の場合も上の構造体の場合と同様に書けるはずですが、要素数が多いと大変なので次に紹介する方法をおすすめします

方法2: 構造体や配列を文字列に変換してGROUP BYする

構造体をFORMAT()関数で文字列に変換して、この文字列でGROUP BYするという手もあります
'%T'へのフォーマットはBigQueryのリテラルの形の文字列になるので(ほとんどの場合)元の構造体でGROUP BYするのと同じような結果が得られるはずです
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#t_and_t_behavior
というわけで以下のように書くことで、だいたいいい感じにSTRUCT型のカラムで集計できるはずです

SELECT
  ANY_VALUE(s) AS s,
  COUNT(*) AS c
FROM (
  SELECT STRUCT(1 AS x, 1 AS y) AS s
  UNION ALL
  SELECT STRUCT(2 AS x, 2 AS y) AS s
  UNION ALL
  SELECT STRUCT(1 AS x, 1 AS y) AS s
)
GROUP BY FORMAT('%T', s)

この方法はそのままARRAY型のカラムにも適用できます
以下のように書くことで配列をキーとした集計ができます

SELECT
  ANY_VALUE(a) AS a,
  COUNT(*) AS c
FROM (
  SELECT [1, 2] AS a
  UNION ALL
  SELECT [1, 2, 3] AS a
  UNION ALL
  SELECT [1, 2] AS a
)
GROUP BY FORMAT('%T', a)