BigQuery、Snowflake、Treasure Data、Athena、MySQL、Oracleの文字列関数について、構文の違いや注意点をまとめました。
📋 関数一覧
文字列長
BigQuery (INT64)
LENGTH(string)
Snowflake (NUMBER)
LENGTH(string)
Treasure Data (BIGINT)
LENGTH(string)
Athena (BIGINT)
LENGTH(string)
MySQL (INT)
LENGTH(string)
Oracle (NUMBER)
LENGTH(string)
Tips
- BigQuery: バイト数ではなく文字数を返す
- Snowflake: NULL入力時はNULLを返す
- Treasure Data: Presto準拠の実装
- Athena: Presto準拠の実装
- MySQL: バイト数を返す点に注意。文字数を取得する場合はCHAR_LENGTH()を使用
- Oracle: 文字数を返す
文字列結合
BigQuery (STRING)
CONCAT(string1, string2, ...)
Snowflake (VARCHAR)
CONCAT(string1, string2, ...)
Treasure Data (VARCHAR)
CONCAT(string1, string2, ...)
Athena (VARCHAR)
CONCAT(string1, string2, ...)
MySQL (VARCHAR)
CONCAT(string1, string2, ...)
Oracle (VARCHAR2)
CONCAT(string1, string2)
Tips
- BigQuery: NULL含む場合はNULLを返す。IFNULL併用で回避
- Snowflake: 任意の数の引数可能。NULL含む場合はNULLを返す。NULL無視はCONCAT_WS使用
- Treasure Data: ||演算子も使用可能
- Athena: ||演算子も使用可能
- MySQL: NULL値が含まれるとNULL
- Oracle: 2つの引数のみ、||推奨
部分文字列
BigQuery (STRING)
SUBSTR(string, start_position[, length])
Snowflake (VARCHAR)
SUBSTR(string, start_position[, length])
Treasure Data (VARCHAR)
SUBSTR(string, start_position[, length])
Athena (VARCHAR)
SUBSTR(string, start_position[, length])
MySQL (VARCHAR)
SUBSTR(string, start_position[, length])
Oracle (VARCHAR2)
SUBSTR(string, start_position[, length])
Tips
- BigQuery: 1ベースインデックス
- Snowflake: 1ベースインデックス
- Treasure Data: 1ベースインデックス
- Athena: 1ベースインデックス
- MySQL: 1ベースインデックス
- Oracle: 負の位置指定可能
大文字変換
BigQuery (STRING)
UPPER(string)
Snowflake (VARCHAR)
UPPER(string)
Treasure Data (VARCHAR)
UPPER(string)
Athena (VARCHAR)
UPPER(string)
MySQL (VARCHAR)
UPPER(string)
Oracle (VARCHAR2)
UPPER(string)
Tips
- BigQuery: Unicode対応
- Snowflake: NULL入力時NULL返却
- MySQL: 文字セット依存
- Oracle: NLS設定依存
小文字変換
BigQuery (STRING)
LOWER(string)
Snowflake (VARCHAR)
LOWER(string)
Treasure Data (VARCHAR)
LOWER(string)
Athena (VARCHAR)
LOWER(string)
MySQL (VARCHAR)
LOWER(string)
Oracle (VARCHAR2)
LOWER(string)
Tips
- BigQuery: Unicode対応
- Snowflake: NULL入力時NULL返却
- MySQL: 文字セット依存
- Oracle: NLS設定依存
空白削除
BigQuery (STRING)
TRIM(string)
Snowflake (VARCHAR)
TRIM(string)
Treasure Data (VARCHAR)
TRIM(string)
Athena (VARCHAR)
TRIM(string)
MySQL (VARCHAR)
TRIM(string)
Oracle (VARCHAR2)
TRIM(string)
Tips
- BigQuery: 前後の空白を削除
- Snowflake: LTRIM/RTRIMも利用可能
- Treasure Data: TRIM([BOTH|LEADING|TRAILING] 文字 FROM string)で任意文字をトリム可能
- Athena: TRIM([BOTH|LEADING|TRAILING] 文字 FROM string)で任意文字をトリム可能
- MySQL: BOTH/LEADING/TRAILING指定可
- Oracle: BOTH/LEADING/TRAILING指定可
文字列置換
BigQuery (STRING)
REPLACE(original_string, from_string, to_string)
Snowflake (VARCHAR)
REPLACE(original_string, from_string, to_string)
Treasure Data (VARCHAR)
REPLACE(original_string, from_string, to_string)
Athena (VARCHAR)
REPLACE(original_string, from_string, to_string)
MySQL (VARCHAR)
REPLACE(original_string, from_string, to_string)
Oracle (VARCHAR2)
REPLACE(original_string, from_string, to_string)
Tips
- BigQuery: 大文字小文字区別
- Snowflake: 全出現箇所を置換
- Treasure Data: NULLは置換されない
- Athena: NULLは置換されない
- MySQL: 大文字小文字区別
- Oracle: to_string省略時は削除
文字列検索
BigQuery (INT64)
INSTR(string, substring)
Snowflake (NUMBER)
POSITION(substring IN string)
Treasure Data (BIGINT)
STRPOS(string, substring)
Athena (BIGINT)
STRPOS(string, substring)
MySQL (INT)
INSTR(string, substring)
Oracle (NUMBER)
INSTR(string, substring[, position[, occurrence]])
Tips
- BigQuery: 最初の出現位置を返す
- Snowflake: 0は見つからない場合
- Treasure Data: 1ベースインデックス
- Athena: 1ベースインデックス
- MySQL: 1ベースインデックス
- Oracle: 開始位置と出現回数指定可
文字列分割
BigQuery (ARRAY)
SPLIT(string, delimiter)
Snowflake (ARRAY)
SPLIT(string, delimiter)
Treasure Data (ARRAY)
SPLIT(string, delimiter)
Athena (ARRAY)
SPLIT(string, delimiter)
MySQL (VARCHAR)
SUBSTRING_INDEX(string, delimiter, count)
Oracle (VARCHAR2)
REGEXP_SUBSTR使用
Tips
- BigQuery: 配列を返す
- Snowflake: 配列を返す
- Treasure Data: 配列を返す
- Athena: 配列を返す
- MySQL: 配列非対応、部分取得のみ
- Oracle: REGEXP_SUBSTRは配列を返さず、N番目の要素を1つ抽出。全要素取得には階層クエリとの組み合わせが必要
正規表現一致
BigQuery (BOOL)
REGEXP_CONTAINS(string, pattern)
Snowflake (BOOLEAN)
REGEXP_LIKE(string, pattern)
Treasure Data (BOOLEAN)
REGEXP_LIKE(string, pattern)
Athena (BOOLEAN)
REGEXP_LIKE(string, pattern)
MySQL (INT)
string REGEXP pattern
Oracle (CONDITION)
REGEXP_LIKE(string, pattern)
Tips
- BigQuery: RE2構文使用
- Snowflake: POSIX構文
- Treasure Data: Java正規表現
- Athena: Java正規表現
- MySQL: MySQL 8.0+はICU。5.7以前はPOSIX。戻り値は0/1の整数（マッチ時1、非マッチ時0）
- Oracle: POSIX ERE準拠。条件式として利用
正規表現置換
BigQuery (STRING)
REGEXP_REPLACE(string, pattern, replacement)
Snowflake (VARCHAR)
REGEXP_REPLACE(string, pattern, replacement)
Treasure Data (VARCHAR)
REGEXP_REPLACE(string, pattern, replacement)
Athena (VARCHAR)
REGEXP_REPLACE(string, pattern, replacement)
MySQL (VARCHAR)
REGEXP_REPLACE(string, pattern, replacement)
Oracle (VARCHAR2)
REGEXP_REPLACE(string, pattern, replacement)
正規表現分割
BigQuery (ARRAY)
REGEXP_EXTRACT_ALL(string, pattern)
Snowflake (TABLE)
SPLIT_TO_TABLE(string, delimiter_pattern)
Treasure Data (ARRAY)
REGEXP_SPLIT(string, pattern)
Athena (ARRAY)
REGEXP_SPLIT(string, pattern)
MySQL (VARCHAR)
REGEXP_SUBSTR(string, pattern, 1, level)
Oracle (VARCHAR2)
REGEXP_SUBSTR(string, pattern, 1, level)
Tips
- BigQuery: REGEXP_EXTRACT_ALLは"抽出"で"分割"の完全代替ではない。分割はREGEXP_REPLACEで区切り記号を正規化→SPLIT()を推奨
- Snowflake: 正規表現で文字列をテーブルに分割
- Treasure Data: 正規表現で文字列を配列に分割
- Athena: 正規表現で文字列を配列に分割
- MySQL: REGEXP_SPLITの直接関数なし。REGEXP_SUBSTRと連番でシミュレート
- Oracle: REGEXP_SPLITの直接関数なし。REGEXP_SUBSTRと階層クエリでシミュレート
正規表現部分文字列抽出
BigQuery (STRING)
REGEXP_EXTRACT(string, pattern)
Snowflake (VARCHAR)
REGEXP_SUBSTR(string, pattern)
Treasure Data (VARCHAR)
REGEXP_EXTRACT(string, pattern)
Athena (VARCHAR)
REGEXP_EXTRACT(string, pattern)
MySQL (VARCHAR)
REGEXP_SUBSTR(string, pattern)
Oracle (VARCHAR2)
REGEXP_SUBSTR(string, pattern)
Tips
- BigQuery: REGEXP_SUBSTRの代わりにREGEXP_EXTRACTを使用
- Snowflake: パターンにマッチする部分文字列を抽出
- Treasure Data: REGEXP_SUBSTRの代わりにREGEXP_EXTRACTを使用
- Athena: REGEXP_SUBSTRの代わりにREGEXP_EXTRACTを使用
- MySQL: パターンにマッチする部分文字列を抽出 (MySQL 8.0.1+)
- Oracle: パターンにマッチする部分文字列を抽出
他のカテゴリ
各DBのバージョンやエディションにより動作が異なる場合があります。本番環境での使用前に必ずテストしてください。