以下の文字列関数は、2 種類のデータ型 STRING
と BYTES
の値を処理できます。STRING
値は、正しい形式の UTF-8 でなければなりません。
STRPOS などの位置値を返す関数は、INT64
としてそれらの位置をエンコードします。値 1
は最初の文字(またはバイト)を表し、2
は 2 番目を表します(以下同様)。値 0
は、無効なインデックスであることを示します。STRING
型を扱っている場合、返される位置は文字の位置です。
すべての文字列比較は、Unicode の標準的な同値に関係なく、バイト単位で行われます。
ASCII
ASCII(value)
説明
value
内の最初の文字またはバイトの ASCII コードを返します。value
が空か、最初の文字の ASCII コードが 0
の場合、0
を返します。
戻り値の型
INT64
例
SELECT ASCII('abcd') as A, ASCII('a') as B, ASCII('') as C, ASCII(NULL) as D;
+-------+-------+-------+-------+
| A | B | C | D |
+-------+-------+-------+-------+
| 97 | 97 | 0 | NULL |
+-------+-------+-------+-------+
BYTE_LENGTH
BYTE_LENGTH(value)
説明
値の型が STRING
または BYTES
のどちらであるかにかかわらず、BYTES
の STRING
値または BYTES
値の長さを返します。
戻り値の型
INT64
例
WITH example AS
(SELECT 'абвгд' AS characters, b'абвгд' AS bytes)
SELECT
characters,
BYTE_LENGTH(characters) AS string_example,
bytes,
BYTE_LENGTH(bytes) AS bytes_example
FROM example;
+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд | 10 | абвгд | 10 |
+------------+----------------+-------+---------------+
CHAR_LENGTH
CHAR_LENGTH(value)
説明
STRING
の長さを文字数で返します。
戻り値の型
INT64
例
WITH example AS
(SELECT 'абвгд' AS characters)
SELECT
characters,
CHAR_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CHARACTER_LENGTH
CHARACTER_LENGTH(value)
説明
CHAR_LENGTH の類義語です。
戻り値の型
INT64
例
WITH example AS
(SELECT 'абвгд' AS characters)
SELECT
characters,
CHARACTER_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CHR
CHR(value)
説明
Unicode コードポイントを取得して、それに一致する文字を返します。各コードポイントは、[0, 0xD7FF] から [0xE000, 0x10FFFF] の範囲です。コードポイントが 0
の場合、空の文字列を返します。無効な Unicode コードポイントが指定されている場合は、エラーが返されます。
Unicode コードポイントの配列を操作する方法については、CODE_POINTS_TO_STRING
をご覧ください。
戻り値の型
STRING
例
SELECT CHR(65) AS A, CHR(255) AS B, CHR(513) AS C, CHR(1024) AS D;
+-------+-------+-------+-------+
| A | B | C | D |
+-------+-------+-------+-------+
| A | ÿ | ȁ | Ѐ |
+-------+-------+-------+-------+
SELECT CHR(97) AS A, CHR(0xF9B5) AS B, CHR(0) AS C, CHR(NULL) AS D;
+-------+-------+-------+-------+
| A | B | C | D |
+-------+-------+-------+-------+
| a | 例 | | NULL |
+-------+-------+-------+-------+
CODE_POINTS_TO_BYTES
CODE_POINTS_TO_BYTES(ascii_code_points)
説明
拡張 ASCII コードポイントの配列(INT64
の ARRAY
)を取り、BYTES
を返します。
BYTES
からコードポイントの配列への変換については、TO_CODE_POINTS をご覧ください。
戻り値の型
BYTES
例
以下は、CODE_POINTS_TO_BYTES
の基本的な使用方法の一例です。
SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;
-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'AbCd' is the result.
+----------+
| bytes |
+----------+
| QWJDZA== |
+----------+
次の例では、ROT13 アルゴリズム(アルファベットの文字列をすべて 13 ずつずれた別の文字に変換する手法)を使用して文字列をエンコードします。
SELECT CODE_POINTS_TO_BYTES(ARRAY_AGG(
(SELECT
CASE
WHEN chr BETWEEN b'a' and b'z'
THEN TO_CODE_POINTS(b'a')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'a')[offset(0)],26)
WHEN chr BETWEEN b'A' and b'Z'
THEN TO_CODE_POINTS(b'A')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'A')[offset(0)],26)
ELSE code
END
FROM
(SELECT code, CODE_POINTS_TO_BYTES([code]) chr)
) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;
-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'Grfg Fgevat!' is the result.
+------------------+
| encoded_string |
+------------------+
| R3JmZyBGZ2V2YXQh |
+------------------+
CODE_POINTS_TO_STRING
CODE_POINTS_TO_STRING(unicode_code_points)
Description
Unicode コードポイントの配列(INT64
の ARRAY
)を取り、STRING
を返します。
文字列からコードポイントの配列への変換については、TO_CODE_POINTS をご覧ください。
戻り値の型
STRING
例
CODE_POINTS_TO_STRING
を使用した基本的な例を次に示します。
SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;
+--------+
| string |
+--------+
| AÿȁЀ |
+--------+
SELECT CODE_POINTS_TO_STRING([97, 0, 0xF9B5]) AS string;
+--------+
| string |
+--------+
| a例 |
+--------+
SELECT CODE_POINTS_TO_STRING([65, 255, NULL, 1024]) AS string;
+--------+
| string |
+--------+
| NULL |
+--------+
以下の例は、一連の単語の中に出現する文字の頻度を計算します。
WITH Words AS (
SELECT word
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
CODE_POINTS_TO_STRING([code_point]) AS letter,
COUNT(*) AS letter_count
FROM Words,
UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;
+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a | 5 |
| f | 3 |
| r | 2 |
| b | 2 |
| l | 2 |
| o | 2 |
| g | 1 |
| z | 1 |
| e | 1 |
| m | 1 |
| i | 1 |
+--------+--------------+
CONCAT
CONCAT(value1[, ...])
説明
1 つ以上の値を 1 つに連結します。すべての値が BYTES
か、STRING
にキャスト可能なデータ型である必要があります。
入力引数が NULL
の場合、この関数は NULL
を返します。
戻り値の型
STRING
または BYTES
例
SELECT CONCAT('T.P.', ' ', 'Bar') as author;
+---------------------+
| author |
+---------------------+
| T.P. Bar |
+---------------------+
SELECT CONCAT('Summer', ' ', 1923) as release_date;
+---------------------+
| release_date |
+---------------------+
| Summer 1923 |
+---------------------+
With Employees AS
(SELECT
'John' AS first_name,
'Doe' AS last_name
UNION ALL
SELECT
'Jane' AS first_name,
'Smith' AS last_name
UNION ALL
SELECT
'Joe' AS first_name,
'Jackson' AS last_name)
SELECT
CONCAT(first_name, ' ', last_name)
AS full_name
FROM Employees;
+---------------------+
| full_name |
+---------------------+
| John Doe |
| Jane Smith |
| Joe Jackson |
+---------------------+
CONTAINS_SUBSTR
CONTAINS_SUBSTR(expression, search_value_literal)
説明
正規化された大文字と小文字を区別しない検索を実行し、式に値が存在するかどうかを確認します。値が存在する場合は TRUE
を返し、存在しない場合は FALSE
を返します。
検索値は STRING
リテラルである必要があり、リテラル NULL
ではありません。
式は列またはテーブルを参照できます。テーブル参照は、フィールドがテーブルの列である STRUCT
として評価されます。列参照は次のデータ型のいずれかとして評価されます。
STRING
INT64
BOOL
NUMERIC
BIGNUMERIC
TIMESTAMP
TIME
DATE
DATETIME
ARRAY
STRUCT
式が評価されると、結果が STRING
にキャストされ、関数が結果の中の検索値を探します。検索値が NULL
の場合、エラーがスローされます。式が NULL
の場合、戻り値は NULL
です。
STRUCT
または ARRAY
と評価された式に対して、クロスフィールド検索を行うことができます。式が STRUCT
と評価された場合、クロスフィールド検索は再帰的になり、STRUCT
内のすべてのサブフィールドが含まれます。
クロスフィールド検索では、各フィールドとサブフィールドが個別に文字列に変換され、値が検索されます。少なくとも 1 つのフィールドに検索値が含まれている場合、この関数は TRUE
を返します。少なくとも 1 つのフィールドが NULL
の場合は NULL
を返します。検索値が見つからず、すべてのフィールドが NULL
でない場合は、FALSE
が返されます。
値を比較する前に、値が正規化され、NFKC
正規化でケース フォールディングが行われます。ワイルドカード検索はサポートされていません。
戻り値の型
BOOL
例
次のクエリは、blue house
と Blue house
という大文字と小文字を区別しない一致があるため、TRUE
を返します。
SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result;
+--------+
| result |
+--------+
| true |
+--------+
次のクエリは、blue
が the red house
で見つからないため、FALSE
を返します。
SELECT CONTAINS_SUBSTR('the red house', 'blue') AS result;
+--------+
| result |
+--------+
| false |
+--------+
次のクエリは、Ⅸ
と IX
が同じ正規化値を示しているため、TRUE
を返します。
SELECT '\u2168 day' AS a, 'IX' AS b, CONTAINS_SUBSTR('\u2168', 'IX') AS result;
+----------------------+
| a | b | result |
+----------------------+
| Ⅸ day | IX | true |
+----------------------+
次のクエリは、35
が STRUCT
フィールド内にあるため、TRUE
を返します。
SELECT CONTAINS_SUBSTR((23, 35, 41), '35') AS result;
+--------+
| result |
+--------+
| true |
+--------+
次のクエリは、STRUCT
内で再帰検索中に jk
が見つかったため、TRUE
を返します。
SELECT CONTAINS_SUBSTR(('abc', ['def', 'ghi', 'jkl'], 'mno'), 'jk');
+--------+
| result |
+--------+
| true |
+--------+
次のクエリは、STRUCT
または ARRAY
内で一致が見つかり NULL
が無視されるため、TRUE
を返します。
SELECT CONTAINS_SUBSTR((23, NULL, 41), '41') AS result;
+--------+
| result |
+--------+
| true |
+--------+
次のクエリは、STRUCT
内に NULL
が存在していますが、一致しなかったため、NULL
を返します。
SELECT CONTAINS_SUBSTR((23, NULL, 41), '35') AS result;
+--------+
| result |
+--------+
| null |
+--------+
次のクエリは、検索値をリテラル NULL
にできないため、エラーがスローされます。
SELECT CONTAINS_SUBSTR('hello', NULL) AS result;
-- Throws an error
以下の例では、次のような WITH
句でエミュレートできる Recipes
というテーブルを参照しています。
WITH Recipes AS
(SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch, 'Potato dumplings' as Dinner UNION ALL
SELECT 'Potato pancakes', 'Toasted cheese sandwich', 'Beef stroganoff' UNION ALL
SELECT 'Ham scramble', 'Steak avocado salad', 'Tomato pasta' UNION ALL
SELECT 'Avocado toast', 'Tomato soup', 'Blueberry salmon' UNION ALL
SELECT 'Corned beef hash', 'Lentil potato soup', 'Glazed ham')
SELECT * FROM Recipes;
+-------------------+-------------------------+------------------+
| Breakfast | Lunch | Dinner |
+-------------------+-------------------------+------------------+
| Bluberry pancakes | Egg salad sandwich | Potato dumplings |
| Potato pancakes | Toasted cheese sandwich | Beef stroganoff |
| Ham scramble | Steak avocado salad | Tomato pasta |
| Avocado toast | Tomato soup | Blueberry samon |
| Corned beef hash | Lentil potato soup | Glazed ham |
+-------------------+-------------------------+------------------+
次のクエリは、Recipes
テーブルのすべての列で値 toast
を検索し、この値を含む行を返します。
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR(Recipes, 'toast');
+-------------------+-------------------------+------------------+
| Breakfast | Lunch | Dinner |
+-------------------+-------------------------+------------------+
| Potato pancakes | Toasted cheese sandwich | Beef stroganoff |
| Avocado toast | Tomato soup | Blueberry samon |
+-------------------+-------------------------+------------------+
次のクエリは、Recipe
テーブルの Lunch
列と Dinner
列で値 potato
を検索し、いずれかの列にこの値が含まれている場合にその行を返します。
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch, Dinner), 'potato');
+-------------------+-------------------------+------------------+
| Breakfast | Lunch | Dinner |
+-------------------+-------------------------+------------------+
| Bluberry pancakes | Egg salad sandwich | Potato dumplings |
| Corned beef hash | Lentil potato soup | Glazed ham |
+-------------------+-------------------------+------------------+
次のクエリは、Recipes
テーブルで Lunch
列と Dinner
列を除くすべての列を検索します。Lunch
と Dinner
以外の列で値 potato
を含む行を返します。
SELECT *
FROM Recipes
WHERE CONTAINS_SUBSTR(
(SELECT AS STRUCT Recipes.* EXCEPT (Lunch, Dinner)),
'potato'
);
+-------------------+-------------------------+------------------+
| Breakfast | Lunch | Dinner |
+-------------------+-------------------------+------------------+
| Potato pancakes | Toasted cheese sandwich | Beef stroganoff |
+-------------------+-------------------------+------------------+
ENDS_WITH
ENDS_WITH(value1, value2)
説明
2 つの STRING
値または BYTES
値を取ります。2 番目の値が 1 番目の値の接尾辞である場合は、TRUE
を返します。
戻り値の型
BOOL
例
WITH items AS
(SELECT 'apple' as item
UNION ALL
SELECT 'banana' as item
UNION ALL
SELECT 'orange' as item)
SELECT
ENDS_WITH(item, 'e') as example
FROM items;
+---------+
| example |
+---------+
| True |
| False |
| True |
+---------+
FORMAT
FORMAT(format_string_expression, data_type_expression[, ...])
説明
FORMAT
は、データ型の式を文字列としてフォーマットします。
format_string_expression
: 0 個以上の形式指定子を含めることができます。形式指定子は%
記号で始まり、1 つ以上の他の引数にマッピングする必要があります。一般に、*
指定子がある場合は除き、これは 1 対 1 のマッピングになります。たとえば、%.*i
は length 引数と符号付き整数引数の 2 つの引数にマッピングされます。形式指定子に関連する引数の数が、これらの引数の数と異なる場合、エラーが発生します。data_type_expression
: 文字列としてフォーマットする値。任意の BigQuery データ型を指定できます。
戻り値の型
STRING
例
説明 | ステートメント | 結果 |
---|---|---|
単純な整数 | FORMAT('%d', 10) | 10 |
左がブランクでパディングされた整数 | FORMAT('|%10d|', 11) | | 11| |
左がゼロでパディングされた整数 | FORMAT('+%010d+', 12) | +0000000012+ |
カンマで区切られた整数 | format("%'d", 123456789) | 123,456,789 |
STRING | FORMAT('-%s-', 'abcd efg') | -abcd efg- |
FLOAT64 | FORMAT('%f %E', 1.1, 2.2) | 1.100000 2.200000E+00 |
日付 | FORMAT('%t', date '2015-09-01') | 2015-09-01 |
TIMESTAMP | FORMAT('%t', timestamp '2015-09-01 12:34:56 America/Los_Angeles') | 2015‑09‑01 19:34:56+00 |
FORMAT()
関数では、すべての型と値に対して完全にカスタマイズ可能な形式設定や、ロケールの影響を受ける形式設定は行えません。
特定の型に対してカスタム形式設定を行う必要がある場合、まず、FORMAT_DATE()
や FORMAT_TIMESTAMP()
などの型固有の形式設定関数を使用して形式設定する必要があります。例:
SELECT FORMAT('date: %s!', FORMAT_DATE('%B %d, %Y', date '2015-01-02'));
戻り値
date: January 02, 2015!
サポートされる形式指定子
%[flags][width][.precision]specifier
値を文字列にキャストするとき、形式指定子により書式設定が追加されます。必要に応じて次のサブ指定子を含めることができます。
形式指定子の追加情報については、以下をご覧ください。
形式指定子
指定子 | 説明 | 例 | 型 |
d または i |
10 進の整数 | 392 |
INT64 |
o |
8 進数 | 610 | INT64* |
x |
16 進の整数 | 7fa | INT64* |
X |
16 進の整数(大文字) | 7FA | INT64* |
f |
有限値の場合は [-](整数部).(小数部)の 10 進表記、非有限値の場合は小文字 | 392.650000 inf nan |
NUMERIC BIGNUMERIC FLOAT64 |
F |
有限値の場合は [-](整数部).(小数部)の 10 進表記、非有限値の場合は大文字 | 392.650000 INF NAN |
NUMERIC BIGNUMERIC FLOAT64 |
e |
科学的記数法(仮数 / 指数)(小文字) | 3.926500e+02 inf nan |
NUMERIC BIGNUMERIC FLOAT64 |
E |
科学的記数法(仮数 / 指数)(大文字) | 3.926500E+02 INF NAN |
NUMERIC BIGNUMERIC FLOAT64 |
g |
入力値の指数と指定された精度に応じて、10 進表記または科学表記のいずれか。小文字で表記します。詳細については、%g と %G の動作をご覧ください。 | 392.65 3.9265e+07 inf nan |
NUMERIC BIGNUMERIC FLOAT64 |
G |
入力値の指数と指定された精度に応じて、10 進表記または科学表記のいずれか。大文字で表記します。詳細については、%g と %G の動作をご覧ください。 | 392.65 3.9265E+07 INF NAN |
NUMERIC BIGNUMERIC FLOAT64 |
p |
JSON を表す出力可能な 1 行の文字列を生成します。%p と %P の動作をご覧ください。 |
{"month":10,"year":2019} |
JSON |
P |
JSON を表す出力可能な複数行の文字列を生成します。%p と %P の動作をご覧ください。 |
{ "month": 10, "year": 2019 } |
JSON |
s |
文字列 | sample | STRING |
t |
値を表す出力可能な文字列を返します。多くの場合、STRING への引数のキャストに似ています。%t と %T の動作をご覧ください。 |
sample 2014‑01‑01 |
<任意> |
T |
値の型と類似した型(幅が広い、または文字列であるなど)の有効な BigQuery 定数である文字列を生成します。%t と %T の動作をご覧ください。 |
'sample' b'bytes sample' 1234 2.3 date '2014‑01‑01' |
<任意> |
% |
「%%」は単一の「%」を生成します。 | % | なし |
* 負の値が使用されている場合は、指定子 %o
、%x
、%X
を指定するとエラーが発生します。
オプションで、形式指定子には指定子プロトタイプの上記のサブ指定子を含めることもできます。
これらのサブ指定子は、次の仕様に従う必要があります。
フラグ
フラグ | 説明 |
- |
所定のフィールド幅内で左側に寄せます。右寄せがデフォルトです(幅のサブ指定子を参照)。 |
+ |
正の数値であっても、結果の前にプラス記号またはマイナス記号(+ または - )を強制的に設定します。デフォルトでは、負の数にのみ - 記号が前に付けられます。 |
<space> | 記号が書き込まれない場合、値の前に空白のスペースが挿入されます。 |
# |
|
0 |
パディングが指定されている場合、スペースではなく、ゼロ(0)で数字の左側にパディングされます(幅サブ指定子を参照)。 |
' |
適切なグルーピング文字を使用して整数を形式設定します。 例:
このフラグは、10 進数、16 進数、および 8 進数の値のみが対象になります。 |
フラグは任意の順序で指定できます。フラグを重複して使用しても、エラーになりません。いずれかの要素タイプにフラグが該当しない場合、それらは無視されます。
幅
幅 | 説明 |
<number> | 印刷される最小文字数です。印刷される値がこの数値よりも短い場合、結果は空白スペースを使用してパディングされます。結果のほうが大きい場合でも、値は切り詰められません。 |
* |
幅は形式設定文字列には指定されませんが、形式設定する必要のある引数の前に追加される整数値引数として指定されます。 |
精度
精度 | 説明 |
. <number> |
|
.* |
精度は、形式設定文字列では指定されませんが、形式設定する必要のある引数の前に追加される整数値引数として指定されます。 |
%g と %G の動作
%g
と %G
の形式指定子は、入力値の指数と指定された精度に応じて、10 進表記(%f
や %F
指定子など)または科学表記(%e
や %E
指定子など)を選択します。
p は指定された精度を表します(デフォルトは 6、指定された精度が 1 未満の場合は 1)。入力値は、最初に精度 = (p - 1) の科学的記数法に変換されます。結果の指数部 x が -4 未満または p 以上の場合、精度 = (p - 1) の科学的記数法が使用されます。それ以外の場合は、精度= (p - 1 - x) の小数表記が使用されます。
#
フラグが存在しない場合は、小数点以下の後続ゼロが削除され、小数点の後に数字がない場合は小数点も削除されます。
%p と %P の動作
%p
形式指定子は出力可能な 1 行の文字列を生成します。%P
形式指定子は出力可能な複数行の文字列を生成します。これらの形式指定子は、次のデータ型で使用できます。
型 | %p | %P |
JSON |
JSON 入力: JSON ' { "month": 10, "year": 2019 } ' JSON を表す出力可能な 1 行の文字列を生成します。 {"month":10,"year":2019} |
JSON 入力: JSON ' { "month": 10, "year": 2019 } ' JSON を表す出力可能な複数行の文字列を生成します。 { "month": 10, "year": 2019 } |
%t と %T の動作
%t
および %T
形式指定子は、すべての型に関して定義されます。幅、精度、フラグは、%s
の場合と同じように機能します。幅は最小幅であり、STRING
はそのサイズにパディングされます。精度は、表示するコンテンツの最大幅です。STRING
は指定されたサイズまで切り詰められてから、幅までパディングされます。
%t
指定子は、常に読み取り可能な形式の値になるように想定されています。
%T
指定子は、常に類似する型の有効な SQL リテラルになります(より広い数値型など)。非定形の浮動小数点値の特殊なケースを除き、リテラルには CAST や型名が含まれません。
STRING
は次のように形式設定されます。
型 | %t | %T |
任意の型の NULL |
NULL | NULL |
INT64 |
123 | 123 |
NUMERIC | 123.0(常に .0 が付く) | NUMERIC "123.0" |
FLOAT64 | 123.0(常に .0 が付く) 123e+10 inf -inf NaN |
123.0(常に .0 が付く) 123e+10 CAST("inf" AS <type>) CAST("-inf" AS <type>) CAST("nan" AS <type>) |
STRING | 引用符で囲まれていない文字列値 | 引用符で囲まれた文字列リテラル |
BYTES | 引用符で囲まれないエスケープ バイト 例: abc\x01\x02 |
引用符付きバイトリテラル 例: b"abc\x01\x02" |
DATE | 2011-02-03 | DATE "2011-02-03" |
TIMESTAMP | 2011-02-03 04:05:06+00 | TIMESTAMP "2011-02-03 04:05:06+00" |
INTERVAL | 1-2 3 4:5:6.789 | INTERVAL "1-2 3 4:5:6.789" YEAR TO SECOND |
ARRAY | [value, value, ...] ここで、値は %t で形式設定される |
[value, value, ...] ここで、値は %T で形式設定される |
STRUCT | (value, value, ...) ここで、フィールドは %t で形式設定される |
(value, value, ...) ここで、フィールドは %T で形式設定される 特別なケース: ゼロ個のフィールド: STRUCT() 1 つのフィールド: STRUCT(value) |
JSON |
JSON を表す出力可能な 1 行の文字列。{"name":"apple","stock":3} |
JSON リテラルを表す出力可能な 1 行の文字列。JSON '{"name":"apple","stock":3}' |
エラー条件
形式指定子が無効な場合や該当する引数型と互換性がない場合、あるいは間違った数や引数が指定されている場合、エラーが生成されます。たとえば、次の <format_string>
式は無効です。
FORMAT('%s', 1)
FORMAT('%')
NULL 引数の処理
NULL
形式文字列は、NULL
の出力 STRING
になります。この場合、その他の引数は無視されます。
NULL
引数がある場合、通常、関数によって NULL
値が生成されます。たとえば、FORMAT('%i', NULL_expression)
は出力として NULL STRING
を生成します。
ただし、いくつかの例外が存在します。形式設定子が %t または %T である(これらはどちらも実際上 CAST とリテラル値セマンティックと一致する STRING
を生成する)場合は、NULL
値によって結果の STRING
内に 'NULL'(引用符なし)が生成されます。たとえば、次の関数の場合です。
FORMAT('00-%t-00', NULL_expression);
戻り値
00-NULL-00
その他のセマンティック ルール
FLOAT64
の値は、+/-inf
または NaN
です。引数にこれらのいずれかの値が含まれている場合、形式指定子 %f
、%F
、%e
、%E
、%g
、%G
、%t
の結果は、状況に応じて inf
、-inf
または nan
(または同じ文字の大文字)になります。これは、BigQuery がこれらの値を STRING
にキャストする方法と一致します。%T
の場合、BigQuery は文字列リテラル表現なしの FLOAT64
値に対して、引用符付きの文字列を返します。
FROM_BASE32
FROM_BASE32(string_expr)
説明
base32 でエンコードされた入力 string_expr
を BYTES
形式に変換します。BYTES
を base32 でエンコードされた STRING
に変換するには、TO_BASE32 を使用します。
戻り値の型
BYTES
例
SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;
-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/ |
+-----------+
FROM_BASE64
FROM_BASE64(string_expr)
説明
base64 でエンコードされた入力 string_expr
を BYTES
形式に変換します。BYTES
を base64 でエンコードされた STRING
に変換するには、TO_BASE64 を使用します。
いくつかの base64 エンコーディングが使用されています。これは、64 桁とパディングのエンコードに、65 個の ASCII 文字の中のどのアルファベットが使用されるかによって異なります。詳しくは、RFC 4648 をご覧ください。この関数は、アルファベット [A-Za-z0-9+/=]
を想定しています。
戻り値の型
BYTES
例
SELECT FROM_BASE64('/+A=') AS byte_data;
-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| /+A= |
+-----------+
別の base64 アルファベットによるエンコードを使用するには、REPLACE
関数を含む FROM_BASE64
の作成が必要になる場合があります。たとえば、ウェブ プログラミングで一般的に使用されている URL セーフおよびファイル名セーフ型の base64url
エンコードでは、最後の文字として +/=
ではなく -_=
を使用します。base64url
でエンコードされた文字列をデコードするには、-
と _
をそれぞれ +
と /
に置き換えます。
SELECT FROM_BASE64(REPLACE(REPLACE('_-A=', '-', '+'), '_', '/')) AS binary;
-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+--------+
| binary |
+--------+
| /+A= |
+--------+
FROM_HEX
FROM_HEX(string)
説明
16 進数でエンコードされた STRING
を BYTES
形式に変換します。入力 STRING
に (0..9, A..F, a..f)
の範囲外の文字が含まれている場合はエラーを返します。文字の大文字と小文字は区別されません。入力 STRING
の文字数が奇数の場合は、入力の先頭に 0
が追加されているものとして処理されます。BYTES
を 16 進数でエンコードされた STRING
に変換するには、TO_HEX を使用します。
戻り値の型
BYTES
例
WITH Input AS (
SELECT '00010203aaeeefff' AS hex_str UNION ALL
SELECT '0AF' UNION ALL
SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;
-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str | bytes_str |
+------------------+--------------+
| 0AF | AAECA6ru7/8= |
| 00010203aaeeefff | AK8= |
| 666f6f626172 | Zm9vYmFy |
+------------------+--------------+
INITCAP
INITCAP(value[, delimiters])
説明
STRING
を取得して、各単語の先頭の文字を大文字に変換し、残りの文字を小文字にして返します。アルファベット以外の文字はそのままです。
delimiters
は、単語の区切りに使用されるデフォルトの文字セットをオーバーライドするオプションの文字列引数です。delimiters
が指定されていない場合、デフォルトで次の文字が使用されます。
<whitespace> [ ] ( ) { } / | \ < > ! ? @ " ^ # $ & ~ _ , . : ; * % + -
value
または delimiters
が NULL
の場合、NULL
を返します。
戻り値の型
STRING
例
WITH example AS
(
SELECT 'Hello World-everyone!' AS value UNION ALL
SELECT 'tHe dog BARKS loudly+friendly' AS value UNION ALL
SELECT 'apples&oranges;&pears' AS value UNION ALL
SELECT 'καθίσματα ταινιών' AS value
)
SELECT value, INITCAP(value) AS initcap_value FROM example
+-------------------------------+-------------------------------+
| value | initcap_value |
+-------------------------------+-------------------------------+
| Hello World-everyone! | Hello World-Everyone! |
| tHe dog BARKS loudly+friendly | The Dog Barks Loudly+Friendly |
| apples&oranges;&pears | Apples&Oranges;&Pears |
| καθίσματα ταινιών | Καθίσματα Ταινιών |
+-------------------------------+-------------------------------+
WITH example AS
(
SELECT 'hello WORLD!' AS value, '' AS delimiters UNION ALL
SELECT 'καθίσματα ταιντιώ@ν' AS value, 'τ@' AS delimiters UNION ALL
SELECT 'Apples1oranges2pears' AS value, '12' AS delimiters UNION ALL
SELECT 'tHisEisEaESentence' AS value, 'E' AS delimiters
)
SELECT value, delimiters, INITCAP(value, delimiters) AS initcap_value FROM example;
+----------------------+------------+----------------------+
| value | delimiters | initcap_value |
+----------------------+------------+----------------------+
| hello WORLD! | | Hello world! |
| καθίσματα ταιντιώ@ν | τ@ | ΚαθίσματΑ τΑιντΙώ@Ν |
| Apples1oranges2pears | 12 | Apples1Oranges2Pears |
| tHisEisEaESentence | E | ThisEIsEAESentence |
+----------------------+------------+----------------------+
INSTR
INSTR(source_value, search_value[, position[, occurrence]])
説明
source_value
内の search_value
の最も小さいインデックス(開始値 1)を返します。一致するものが見つからない場合、0 が返されます。source_value
と search_value
は同じ型(STRING
または BYTES
)にする必要があります。
position
を指定すると、source_value
のこの位置から検索が開始します。それ以外の場合は、source_value
の先頭から開始します。position
が負の値である場合、source_value
の最後から逆方向に検索され、-1 は最後の文字を指します。position
を 0 にすることはできません。
occurrence
を指定すると、source_value
の search_value
の特定のインスタンスの位置が返されます。それ以外の場合は、最初の出現位置のインデックスが返されます。occurrence
が見つかった一致数より大きい場合は、0 が返されます。occurrence
> 1 の場合、この関数は重複する出現を検索します。つまり、前の出現の 2 番目の文字で別の出現を検索します。occurrence
を 0 または負の値にすることはできません。
戻り値の型
INT64
例
WITH example AS
(SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 2 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 3 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'ann' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 2 as
occurrence
)
SELECT source_value, search_value, position, occurrence, INSTR(source_value,
search_value, position, occurrence) AS instr
FROM example;
+--------------+--------------+----------+------------+-------+
| source_value | search_value | position | occurrence | instr |
+--------------+--------------+----------+------------+-------+
| banana | an | 1 | 1 | 2 |
| banana | an | 1 | 2 | 4 |
| banana | an | 1 | 3 | 0 |
| banana | an | 3 | 1 | 4 |
| banana | an | -1 | 1 | 4 |
| banana | an | -3 | 1 | 4 |
| banana | ann | 1 | 1 | 0 |
| helloooo | oo | 1 | 1 | 5 |
| helloooo | oo | 1 | 2 | 6 |
+--------------+--------------+----------+------------+-------+
LEFT
LEFT(value, length)
説明
STRING
または BYTES
値を返します。これは、value
の左端から指定された文字数またはバイト数で構成されます。length
は、戻り値の長さを指定する INT64
です。value
の型が BYTES
の場合、length
は左端からのバイト数です。value
が STRING
の場合、length
は左端からの文字数です。
length
が 0 の場合、空の STRING
または BYTES
値が返されます。length
が負の数の場合、エラーが返されます。length
が value
の文字数またはバイト数を超えると、元の value
が返されます。
戻り値の型
STRING
または BYTES
例
WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;
+---------+--------------+
| example | left_example |
+---------+--------------+
| apple | app |
| banana | ban |
| абвгд | абв |
+---------+--------------+
WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;
-- Note that the result of LEFT is of type BYTES, displayed as a base64-encoded string.
+----------+--------------+
| example | left_example |
+----------+--------------+
| YXBwbGU= | YXBw |
| YmFuYW5h | YmFu |
| q83vqrs= | q83v |
+----------+--------------+
LENGTH
LENGTH(value)
説明
STRING
値または BYTES
値の長さを返します。戻り値は STRING
引数の場合は文字数、BYTES
引数の場合はバイト数で示されます。
戻り値の型
INT64
例
WITH example AS
(SELECT 'абвгд' AS characters)
SELECT
characters,
LENGTH(characters) AS string_example,
LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;
+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд | 5 | 10 |
+------------+----------------+---------------+
LPAD
LPAD(original_value, return_length[, pattern])
説明
先頭に pattern
が追加された original_value
で構成される STRING
値または BYTES
値を返します。return_length
は、戻り値の長さを指定する INT64
です。original_value
が BYTES
型の場合、return_length
はバイト数です。original_value
が STRING
型の場合、return_length
は文字数です。
pattern
のデフォルト値はスペースです。
original_value
と pattern
は同じデータ型にする必要があります。
return_length
が original_value
以下である場合、この関数は original_value
の値を return_length
の値まで切り詰めて返します。たとえば、LPAD('hello world', 7);
は 'hello w'
を返します。
original_value
、return_length
、または pattern
が NULL
の場合、この関数は NULL
を返します。
次の場合、この関数はエラーを返します。
return_length
が負の値であるpattern
が空白である
戻り値の型
STRING
または BYTES
例
SELECT t, len, FORMAT('%T', LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
+------+-----+----------+
| t | len | LPAD |
|------|-----|----------|
| abc | 5 | " abc" |
| abc | 2 | "ab" |
| 例子 | 4 | " 例子" |
+------+-----+----------+
SELECT t, len, pattern, FORMAT('%T', LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
+------+-----+---------+--------------+
| t | len | pattern | LPAD |
|------|-----|---------|--------------|
| abc | 8 | def | "defdeabc" |
| abc | 5 | - | "--abc" |
| 例子 | 5 | 中文 | "中文中例子" |
+------+-----+---------+--------------+
SELECT FORMAT('%T', t) AS t, len, FORMAT('%T', LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
+-----------------+-----+------------------+
| t | len | LPAD |
|-----------------|-----|------------------|
| b"abc" | 5 | b" abc" |
| b"abc" | 2 | b"ab" |
| b"\xab\xcd\xef" | 4 | b" \xab\xcd\xef" |
+-----------------+-----+------------------+
SELECT
FORMAT('%T', t) AS t,
len,
FORMAT('%T', pattern) AS pattern,
FORMAT('%T', LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
+-----------------+-----+---------+-------------------------+
| t | len | pattern | LPAD |
|-----------------|-----|---------|-------------------------|
| b"abc" | 8 | b"def" | b"defdeabc" |
| b"abc" | 5 | b"-" | b"--abc" |
| b"\xab\xcd\xef" | 5 | b"\x00" | b"\x00\x00\xab\xcd\xef" |
+-----------------+-----+---------+-------------------------+
LOWER
LOWER(value)
説明
STRING
引数の場合は、元の文字列をすべて小文字の英字で返します。小文字と大文字の間のマッピングは、言語固有のマッピングを考慮せずに、Unicode 文字データベースに従って行われます。
BYTES
引数の場合、引数は ASCII テキストとして処理され、127 を超えるすべてのバイトはそのまま残ります。
戻り値の型
STRING
または BYTES
例
WITH items AS
(SELECT
'FOO' as item
UNION ALL
SELECT
'BAR' as item
UNION ALL
SELECT
'BAZ' as item)
SELECT
LOWER(item) AS example
FROM items;
+---------+
| example |
+---------+
| foo |
| bar |
| baz |
+---------+
LTRIM
LTRIM(value1[, value2])
説明
TRIM と同じですが、先頭からのみ文字を削除します。
戻り値の型
STRING
または BYTES
例
WITH items AS
(SELECT ' apple ' as item
UNION ALL
SELECT ' banana ' as item
UNION ALL
SELECT ' orange ' as item)
SELECT
CONCAT('#', LTRIM(item), '#') as example
FROM items;
+-------------+
| example |
+-------------+
| #apple # |
| #banana # |
| #orange # |
+-------------+
WITH items AS
(SELECT '***apple***' as item
UNION ALL
SELECT '***banana***' as item
UNION ALL
SELECT '***orange***' as item)
SELECT
LTRIM(item, '*') as example
FROM items;
+-----------+
| example |
+-----------+
| apple*** |
| banana*** |
| orange*** |
+-----------+
WITH items AS
(SELECT 'xxxapplexxx' as item
UNION ALL
SELECT 'yyybananayyy' as item
UNION ALL
SELECT 'zzzorangezzz' as item
UNION ALL
SELECT 'xyzpearxyz' as item)
SELECT
LTRIM(item, 'xyz') as example
FROM items;
+-----------+
| example |
+-----------+
| applexxx |
| bananayyy |
| orangezzz |
| pearxyz |
+-----------+
NORMALIZE
NORMALIZE(value[, normalization_mode])
説明
文字列値を取り、正規化された文字列として返します。正規化モードを指定しない場合は、NFC
が使用されます。
正規化は、2 つの文字列を同等にするために使用されます。正規化は、2 つの文字列が画面上で同じように表示され、異なる Unicode コードポイントを持つ状況でよく使用されます。
NORMALIZE
は、4 つのオプション正規化モードをサポートしています。
値 | 名前 | 説明 |
---|---|---|
NFC |
正規化形式 C | 標準的な同値によって文字を分解し、再構成する。 |
NFKC |
正規化形式 KC | 互換性によって文字を分解し、標準的な同値によって再構成する。 |
NFD |
正規化形式 D | 標準的な同値によって文字を分解し、複数の結合文字を特定の順序で並べる。 |
NFKD |
正規化形式 KD | 互換性によって文字を分解し、複数の結合文字を特定の順序で並べる。 |
戻り値の型
STRING
例
SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b);
+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true |
+---+---+------------+
次の例では、複数のスペース文字を正規化しています。
WITH EquivalentNames AS (
SELECT name
FROM UNNEST([
'Jane\u2004Doe',
'John\u2004Smith',
'Jane\u2005Doe',
'Jane\u2006Doe',
'John Smith']) AS name
)
SELECT
NORMALIZE(name, NFKC) AS normalized_name,
COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;
+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith | 2 |
| Jane Doe | 3 |
+-----------------+------------+
NORMALIZE_AND_CASEFOLD
NORMALIZE_AND_CASEFOLD(value[, normalization_mode])
説明
文字列値を取り、正規化された文字列として返します。正規化モードを指定しない場合は、NFC
が使用されます。
正規化は、2 つの文字列を同等にするために使用されます。正規化は、2 つの文字列が画面上で同じように表示され、異なる Unicode コードポイントを持つ状況でよく使用されます。
ケース フォールディングは、大文字と小文字を区別せずに文字列を比較するために使用されます。文字列を比較する必要があり、大文字と小文字を区別しない場合は、NORMALIZE_AND_CASEFOLD
を使用します。区別する場合は NORMALIZE
を使用します。
NORMALIZE_AND_CASEFOLD
は、4 つのオプション正規化モードをサポートしています。
値 | 名前 | 説明 |
---|---|---|
NFC |
正規化形式 C | 標準的な同値によって文字を分解し、再構成する。 |
NFKC |
正規化形式 KC | 互換性によって文字を分解し、標準的な同値によって再構成する。 |
NFD |
正規化形式 D | 標準的な同値によって文字を分解し、複数の結合文字を特定の順序で並べる。 |
NFKD |
正規化形式 KD | 互換性によって文字を分解し、複数の結合文字を特定の順序で並べる。 |
戻り値の型
STRING
例
SELECT
a, b,
NORMALIZE(a) = NORMALIZE(b) as normalized,
NORMALIZE_AND_CASEFOLD(a) = NORMALIZE_AND_CASEFOLD(b) as normalized_with_case_folding
FROM (SELECT 'The red barn' AS a, 'The Red Barn' AS b);
+--------------+--------------+------------+------------------------------+
| a | b | normalized | normalized_with_case_folding |
+--------------+--------------+------------+------------------------------+
| The red barn | The Red Barn | false | true |
+--------------+--------------+------------+------------------------------+
WITH Strings AS (
SELECT '\u2168' AS a, 'IX' AS b UNION ALL
SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;
+---+----+-------+-------+------+------+
| a | b | nfd | nfc | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å | true | true | true | true |
+---+----+-------+-------+------+------+
OCTET_LENGTH
OCTET_LENGTH(value)
BYTE_LENGTH
のエイリアス。
REGEXP_CONTAINS
REGEXP_CONTAINS(value, regexp)
説明
value
が正規表現 regexp
に対して部分一致である場合は、TRUE
を返します。
regexp
の引数が無効な場合は、関数はエラーを返します。
完全一致を検索するには、^
(テキストの先頭)と $
(テキストの末尾)を使用します。正規表現演算子が優先されるため、^
と $
間のすべてのものを括弧で囲むことをおすすめします。
戻り値の型
BOOL
例
SELECT
email,
REGEXP_CONTAINS(email, r'@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+') AS is_valid
FROM
(SELECT
['foo@example.com', 'bar@example.org', 'www.example.net']
AS addresses),
UNNEST(addresses) AS email;
+-----------------+----------+
| email | is_valid |
+-----------------+----------+
| foo@example.com | true |
| bar@example.org | true |
| www.example.net | false |
+-----------------+----------+
-- Performs a full match, using ^ and $. Due to regular expression operator
-- precedence, it is good practice to use parentheses around everything between ^
-- and $.
SELECT
email,
REGEXP_CONTAINS(email, r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$')
AS valid_email_address,
REGEXP_CONTAINS(email, r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$')
AS without_parentheses
FROM
(SELECT
['a@foo.com', 'a@foo.computer', 'b@bar.org', '!b@bar.org', 'c@buz.net']
AS addresses),
UNNEST(addresses) AS email;
+----------------+---------------------+---------------------+
| email | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com | true | true |
| a@foo.computer | false | true |
| b@bar.org | true | true |
| !b@bar.org | false | true |
| c@buz.net | false | false |
+----------------+---------------------+---------------------+
REGEXP_EXTRACT
REGEXP_EXTRACT(value, regexp[, position[, occurrence]])
説明
正規表現 regexp
と一致する value
内の部分文字列を返します。一致がない場合、NULL
を返します。
正規表現がキャプチャ グループを含む場合、この関数はそのキャプチャ グループによって照合された部分文字列を返します。式にキャプチャ グループが含まれていない場合、この関数によって一致しているすべての部分文字列が返されます。
position
を指定すると、value
のこの位置から検索が開始します。それ以外の場合は、value
の先頭から開始します。position
は正の整数にする必要があります。0 にはできません。position
が value
の長さより大きい場合、NULL
が返されます。
occurrence
を指定すると、value
の regexp
の特定の一致が返されます。それ以外の場合は、最初の一致が返されます。occurrence
が一致した数より大きい場合、NULL
が返されます。occurrence
> 1 の場合、前の出現の後続文字で始まる別の出現を検索します。
次の場合にエラーを返します。
- 正規表現が無効の場合。
- 正規表現に複数のキャプチャ グループがある場合。
position
が正の整数でない場合。occurrence
が正の整数でない場合。
戻り値の型
STRING
または BYTES
例
WITH email_addresses AS
(SELECT 'foo@example.com' as email
UNION ALL
SELECT 'bar@example.org' as email
UNION ALL
SELECT 'baz@example.net' as email)
SELECT
REGEXP_EXTRACT(email, r'^[a-zA-Z0-9_.+-]+')
AS user_name
FROM email_addresses;
+-----------+
| user_name |
+-----------+
| foo |
| bar |
| baz |
+-----------+
WITH email_addresses AS
(SELECT 'foo@example.com' as email
UNION ALL
SELECT 'bar@example.org' as email
UNION ALL
SELECT 'baz@example.net' as email)
SELECT
REGEXP_EXTRACT(email, r'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)')
AS top_level_domain
FROM email_addresses;
+------------------+
| top_level_domain |
+------------------+
| com |
| org |
| net |
+------------------+
WITH example AS
(SELECT 'Hello Helloo and Hellooo' AS value, 'H?ello+' AS regex, 1 as position,
1 AS occurrence UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 4 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 2, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 20, 1 UNION ALL
SELECT 'cats&dogs&rabbits' ,'\\w+&', 1, 2 UNION ALL
SELECT 'cats&dogs&rabbits', '\\w+&', 2, 3
)
SELECT value, regex, position, occurrence, REGEXP_EXTRACT(value, regex,
position, occurrence) AS regexp_value FROM example;
+--------------------------+---------+----------+------------+--------------+
| value | regex | position | occurrence | regexp_value |
+--------------------------+---------+----------+------------+--------------+
| Hello Helloo and Hellooo | H?ello+ | 1 | 1 | Hello |
| Hello Helloo and Hellooo | H?ello+ | 1 | 2 | Helloo |
| Hello Helloo and Hellooo | H?ello+ | 1 | 3 | Hellooo |
| Hello Helloo and Hellooo | H?ello+ | 1 | 4 | NULL |
| Hello Helloo and Hellooo | H?ello+ | 2 | 1 | ello |
| Hello Helloo and Hellooo | H?ello+ | 3 | 1 | Helloo |
| Hello Helloo and Hellooo | H?ello+ | 3 | 2 | Hellooo |
| Hello Helloo and Hellooo | H?ello+ | 3 | 3 | NULL |
| Hello Helloo and Hellooo | H?ello+ | 20 | 1 | NULL |
| cats&dogs&rabbits | \w+& | 1 | 2 | dogs& |
| cats&dogs&rabbits | \w+& | 2 | 3 | NULL |
+--------------------------+---------+----------+------------+--------------+
REGEXP_EXTRACT_ALL
REGEXP_EXTRACT_ALL(value, regexp)
説明
正規表現 regexp
と一致する value
のすべての部分文字列の配列を返します。
REGEXP_EXTRACT_ALL
関数は、重複しない一致のみを返します。たとえば、この関数を使用して banana
から ana
を抽出した場合、返される部分文字列は 2 つではなく、1 つのみです。
戻り値の型
STRING
または BYTES
の ARRAY
例
WITH code_markdown AS
(SELECT 'Try `function(x)` or `function(y)`' as code)
SELECT
REGEXP_EXTRACT_ALL(code, '`(.+?)`') AS example
FROM code_markdown;
+----------------------------+
| example |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+
REGEXP_INSTR
REGEXP_INSTR(source_value, regexp [, position[, occurrence, [occurrence_position]]])
説明
正規表現 regexp
のうち、最も小さいインデックス(開始値 1)を source_value
で返します。一致が見つからないか、正規表現が空の場合、0
を返します。正規表現が無効であるか、キャプチャ グループが複数存在する場合は、エラーを返します。source_value
と regexp
は同じ型(STRING
または BYTES
)にする必要があります。
position
を指定すると、source_value
のこの位置から検索が開始します。それ以外の場合は、source_value
の先頭から開始します。
position
を 0 または負の値にすることはできません。
occurrence
を指定すると、source_value
の regexp
の特定のインスタンスの位置が返されます。それ以外の場合は、最初の出現位置のインデックスが返されます。occurrence
が見つかった一致数より大きい場合は、0 が返されます。occurrence
> 1 の場合、この関数は重複する出現を検索します。つまり、前の出現の 2 番目の文字で別の出現を検索します。occurrence
を 0 または負の値にすることはできません。
必要に応じて、occurrence_position
を使用して、occurrence
に関連する位置の開始位置を指定できます。+ 0
を選択した場合、出現位置の始点が返されます。+ 1
を選択した場合、出現位置の末尾の最初の位置が返されます。出現位置の末尾が入力の終わりの場合、出現位置の末尾が 1 回だけ返されます。例: 文字列 + 1。
戻り値の型
INT64
例
WITH example AS (
SELECT 'ab@gmail.com' AS source_value, '@[^.]*' AS regexp UNION ALL
SELECT 'ab@mail.com', '@[^.]*' UNION ALL
SELECT 'abc@gmail.com', '@[^.]*' UNION ALL
SELECT 'abc.com', '@[^.]*')
SELECT source_value, regexp, REGEXP_INSTR(source_value, regexp) AS instr
FROM example;
+---------------+--------+-------+
| source_value | regexp | instr |
+---------------+--------+-------+
| ab@gmail.com | @[^.]* | 3 |
| ab@mail.com | @[^.]* | 3 |
| abc@gmail.com | @[^.]* | 4 |
| abc.com | @[^.]* | 0 |
+---------------+--------+-------+
WITH example AS (
SELECT 'a@gmail.com b@gmail.com' AS source_value, '@[^.]*' AS regexp, 1 AS position UNION ALL
SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 2 UNION ALL
SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 3 UNION ALL
SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 4)
SELECT
source_value, regexp, position,
REGEXP_INSTR(source_value, regexp, position) AS instr
FROM example;
+-------------------------+--------+----------+-------+
| source_value | regexp | position | instr |
+-------------------------+--------+----------+-------+
| a@gmail.com b@gmail.com | @[^.]* | 1 | 2 |
| a@gmail.com b@gmail.com | @[^.]* | 2 | 2 |
| a@gmail.com b@gmail.com | @[^.]* | 3 | 14 |
| a@gmail.com b@gmail.com | @[^.]* | 4 | 14 |
+-------------------------+--------+----------+-------+
WITH example AS (
SELECT 'a@gmail.com b@gmail.com c@gmail.com' AS source_value,
'@[^.]*' AS regexp, 1 AS position, 1 AS occurrence UNION ALL
SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 2 UNION ALL
SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 3)
SELECT
source_value, regexp, position, occurrence,
REGEXP_INSTR(source_value, regexp, position, occurrence) AS instr
FROM example;
+-------------------------------------+--------+----------+------------+-------+
| source_value | regexp | position | occurrence | instr |
+-------------------------------------+--------+----------+------------+-------+
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1 | 1 | 2 |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1 | 2 | 14 |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1 | 3 | 26 |
+-------------------------------------+--------+----------+------------+-------+
WITH example AS (
SELECT 'a@gmail.com' AS source_value, '@[^.]*' AS regexp,
1 AS position, 1 AS occurrence, 0 AS o_position UNION ALL
SELECT 'a@gmail.com', '@[^.]*', 1, 1, 1)
SELECT
source_value, regexp, position, occurrence, o_position,
REGEXP_INSTR(source_value, regexp, position, occurrence, o_position) AS instr
FROM example;
+--------------+--------+----------+------------+------------+-------+
| source_value | regexp | position | occurrence | o_position | instr |
+--------------+--------+----------+------------+------------+-------+
| a@gmail.com | @[^.]* | 1 | 1 | 0 | 2 |
| a@gmail.com | @[^.]* | 1 | 1 | 1 | 8 |
+--------------+--------+----------+------------+------------+-------+
REGEXP_REPLACE
REGEXP_REPLACE(value, regexp, replacement)
説明
正規表現 regexp
と一致する value
のすべての部分文字列を replacement
に置き換えた STRING
を返します。
replacement
引数内でバックスラッシュでエスケープされた数字(\1~\9)を使用して、対応する括弧で囲まれたグループと一致するテキストを regexp
パターン内に挿入できます。一致するテキスト全体を参照するには、\0 を使用します。
正規表現にバックスラッシュを追加するには、あらかじめエスケープしておく必要があります。たとえば、SELECT REGEXP_REPLACE('abc', 'b(.)', 'X\\1');
は aXc
を返します。また、元の文字列を使用して、エスケープの 1 つのレイヤを削除できます(例: SELECT REGEXP_REPLACE('abc', 'b(.)', r'X\1');
)。
REGEXP_REPLACE
関数は、重複しない一致のみを置き換えます。たとえば、banana
に含まれる ana
を置き換える場合、置き換えられるのは 2 つではなく、1 つのみです。
regexp
の引数が有効な正規表現でない場合、この関数はエラーを返します。
戻り値の型
STRING
または BYTES
例
WITH markdown AS
(SELECT '# Heading' as heading
UNION ALL
SELECT '# Another heading' as heading)
SELECT
REGEXP_REPLACE(heading, r'^# ([a-zA-Z0-9\s]+$)', '<h1>\\1</h1>')
AS html
FROM markdown;
+--------------------------+
| html |
+--------------------------+
| <h1>Heading</h1> |
| <h1>Another heading</h1> |
+--------------------------+
REGEXP_SUBSTR
REGEXP_SUBSTR(value, regexp[, position[, occurrence]])
説明
REGEXP_EXTRACT の類義語。
戻り値の型
STRING
または BYTES
例
WITH example AS
(SELECT 'Hello World Helloo' AS value, 'H?ello+' AS regex, 1 AS position, 1 AS
occurrence
)
SELECT value, regex, position, occurrence, REGEXP_SUBSTR(value, regex,
position, occurrence) AS regexp_value FROM example;
+--------------------+---------+----------+------------+--------------+
| value | regex | position | occurrence | regexp_value |
+--------------------+---------+----------+------------+--------------+
| Hello World Helloo | H?ello+ | 1 | 1 | Hello |
+--------------------+---------+----------+------------+--------------+
REPLACE
REPLACE(original_value, from_value, to_value)
説明
original_value
内に出現するすべての from_value
を to_value
に置き換えますfrom_value
が空の場合、置き換えは行われません。
戻り値の型
STRING
または BYTES
例
WITH desserts AS
(SELECT 'apple pie' as dessert
UNION ALL
SELECT 'blackberry pie' as dessert
UNION ALL
SELECT 'cherry pie' as dessert)
SELECT
REPLACE (dessert, 'pie', 'cobbler') as example
FROM desserts;
+--------------------+
| example |
+--------------------+
| apple cobbler |
| blackberry cobbler |
| cherry cobbler |
+--------------------+
REPEAT
REPEAT(original_value, repetitions)
説明
original_value
の繰り返しで構成される STRING
値または BYTES
値を返します。repetitions
パラメータで、original_value
の繰り返しの数を指定します。original_value
または repetitions
のいずれかが NULL
の場合は、NULL
を返します。
repetitions
の値が負の数の場合、この関数はエラーを返します。
戻り値の型
STRING
または BYTES
例
SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
STRUCT('abc' AS t, 3 AS n),
('例子', 2),
('abc', null),
(null, 3)
]);
+------+------+-----------+
| t | n | REPEAT |
|------|------|-----------|
| abc | 3 | abcabcabc |
| 例子 | 2 | 例子例子 |
| abc | NULL | NULL |
| NULL | 3 | NULL |
+------+------+-----------+
REVERSE
REVERSE(value)
説明
入力の STRING
または BYTES
を逆順で返します。
戻り値の型
STRING
または BYTES
例
WITH example AS (
SELECT 'foo' AS sample_string, b'bar' AS sample_bytes UNION ALL
SELECT 'абвгд' AS sample_string, b'123' AS sample_bytes
)
SELECT
sample_string,
REVERSE(sample_string) AS reverse_string,
sample_bytes,
REVERSE(sample_bytes) AS reverse_bytes
FROM example;
+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo | oof | bar | rab |
| абвгд | дгвба | 123 | 321 |
+---------------+----------------+--------------+---------------+
RIGHT
RIGHT(value, length)
説明
STRING
または BYTES
値を返します。これは、value
の右端から指定された文字数またはバイト数で構成されます。length
は、戻り値の長さを指定する INT64
です。value
が BYTES
の場合、length
は右端からのバイト数です。value
が STRING
の場合、length
は右端からの文字数です。
length
が 0 の場合、空の STRING
または BYTES
値が返されます。length
が負の数の場合、エラーが返されます。length
が value
の文字数またはバイト数を超えると、元の value
が返されます。
戻り値の型
STRING
または BYTES
例
WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;
+---------+---------------+
| example | right_example |
+---------+---------------+
| apple | ple |
| banana | ana |
| абвгд | вгд |
+---------+---------------+
WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;
-- Note that the result of RIGHT is of type BYTES, displayed as a base64-encoded string.
+----------+---------------+
| example | right_example |
+----------+---------------+
| YXBwbGU= | cGxl |
| YmFuYW5h | YW5h |
| q83vqrs= | 76q7 |
+----------+---------------+
RPAD
RPAD(original_value, return_length[, pattern])
説明
pattern
が追加された original_value
で構成される STRING
値または BYTES
値を返します。return_length
パラメータは、戻り値の長さを指定する INT64
です。original_value
が BYTES
の場合、return_length
はバイト数です。original_value
が STRING
の場合、return_length
は文字数です。
pattern
のデフォルト値はスペースです。
original_value
と pattern
は同じデータ型にする必要があります。
return_length
が original_value
以下である場合、この関数は original_value
の値を return_length
の値まで切り詰めて返します。たとえば、RPAD('hello world', 7);
は 'hello w'
を返します。
original_value
、return_length
、または pattern
が NULL
の場合、この関数は NULL
を返します。
次の場合、この関数はエラーを返します。
return_length
が負の値であるpattern
が空白である
戻り値の型
STRING
または BYTES
例
SELECT t, len, FORMAT('%T', RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
+------+-----+----------+
| t | len | RPAD |
|------|-----|----------|
| abc | 5 | "abc " |
| abc | 2 | "ab" |
| 例子 | 4 | "例子 " |
+------+-----+----------+
SELECT t, len, pattern, FORMAT('%T', RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
+------+-----+---------+--------------+
| t | len | pattern | RPAD |
|------|-----|---------|--------------|
| abc | 8 | def | "abcdefde" |
| abc | 5 | - | "abc--" |
| 例子 | 5 | 中文 | "例子中文中" |
+------+-----+---------+--------------+
SELECT FORMAT('%T', t) AS t, len, FORMAT('%T', RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
+-----------------+-----+------------------+
| t | len | RPAD |
|-----------------|-----|------------------|
| b"abc" | 5 | b"abc " |
| b"abc" | 2 | b"ab" |
| b"\xab\xcd\xef" | 4 | b"\xab\xcd\xef " |
+-----------------+-----+------------------+
SELECT
FORMAT('%T', t) AS t,
len,
FORMAT('%T', pattern) AS pattern,
FORMAT('%T', RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
+-----------------+-----+---------+-------------------------+
| t | len | pattern | RPAD |
|-----------------|-----|---------|-------------------------|
| b"abc" | 8 | b"def" | b"abcdefde" |
| b"abc" | 5 | b"-" | b"abc--" |
| b"\xab\xcd\xef" | 5 | b"\x00" | b"\xab\xcd\xef\x00\x00" |
+-----------------+-----+---------+-------------------------+
RTRIM
RTRIM(value1[, value2])
説明
TRIM と同一ですが、文字を末尾からのみ削除します。
戻り値の型
STRING
または BYTES
例
WITH items AS
(SELECT '***apple***' as item
UNION ALL
SELECT '***banana***' as item
UNION ALL
SELECT '***orange***' as item)
SELECT
RTRIM(item, '*') as example
FROM items;
+-----------+
| example |
+-----------+
| ***apple |
| ***banana |
| ***orange |
+-----------+
WITH items AS
(SELECT 'applexxx' as item
UNION ALL
SELECT 'bananayyy' as item
UNION ALL
SELECT 'orangezzz' as item
UNION ALL
SELECT 'pearxyz' as item)
SELECT
RTRIM(item, 'xyz') as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
SAFE_CONVERT_BYTES_TO_STRING
SAFE_CONVERT_BYTES_TO_STRING(value)
説明
BYTES
のシーケンスを STRING
に変換します。無効な UTF-8 文字列は Unicode 置換文字 U+FFFD
に置き換えられます。
戻り値の型
STRING
例
次のステートメントは、Unicode 置換文字 � を返します。
SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;
SOUNDEX
SOUNDEX(value)
説明
value
の Soundex コードを表す STRING
を返します。
SOUNDEX は、文字列の発音を表す表現を生成します。英語の発音どおりに、音声で単語をインデックスに登録します。通常は 2 つの文字列を判別するために使用されます。たとえば、Levine と Lavine のような名前や、to と too など発音が似ている文字列の判別に使用されます。
SOUNDEX の結果は、文字の後に 3 桁の数字が続きます。ラテン文字以外の文字は無視されます。ラテン文字以外を削除した後、残りの文字列が空になると、空の STRING
が返されます。
戻り値の型
STRING
例
WITH example AS (
SELECT 'Ashcraft' AS value UNION ALL
SELECT 'Raven' AS value UNION ALL
SELECT 'Ribbon' AS value UNION ALL
SELECT 'apple' AS value UNION ALL
SELECT 'Hello world!' AS value UNION ALL
SELECT ' H3##!@llo w00orld!' AS value UNION ALL
SELECT '#1' AS value UNION ALL
SELECT NULL AS value
)
SELECT value, SOUNDEX(value) AS soundex
FROM example;
+----------------------+---------+
| value | soundex |
+----------------------+---------+
| Ashcraft | A261 |
| Raven | R150 |
| Ribbon | R150 |
| apple | a140 |
| Hello world! | H464 |
| H3##!@llo w00orld! | H464 |
| #1 | |
| NULL | NULL |
+----------------------+---------+
SPLIT
SPLIT(value[, delimiter])
説明
delimiter
引数を使用して value
を分割します。
STRING
の場合、デフォルトの区切り文字はカンマ(,
)です。
BYTES
の場合は、区切り文字を指定する必要があります。
空の区切り文字で分割すると、STRING
値の場合は UTF-8 文字の配列が生成され、BYTES
値の場合は BYTES
の配列が生成されます。
空の STRING
を分割すると、1 つの空の STRING
を持つ ARRAY
が返されます。
戻り値の型
STRING
型の ARRAY
、または BYTES
型の ARRAY
例
WITH letters AS
(SELECT '' as letter_group
UNION ALL
SELECT 'a' as letter_group
UNION ALL
SELECT 'b c d' as letter_group)
SELECT SPLIT(letter_group, ' ') as example
FROM letters;
+----------------------+
| example |
+----------------------+
| [] |
| [a] |
| [b, c, d] |
+----------------------+
STARTS_WITH
STARTS_WITH(value1, value2)
説明
2 つの STRING
値または BYTES
値を取ります。2 番目の値が 1 番目の値の接頭辞である場合は、TRUE
を返します。
戻り値の型
BOOL
例
WITH items AS
(SELECT 'foo' as item
UNION ALL
SELECT 'bar' as item
UNION ALL
SELECT 'baz' as item)
SELECT
STARTS_WITH(item, 'b') as example
FROM items;
+---------+
| example |
+---------+
| False |
| True |
| True |
+---------+
STRPOS
STRPOS(value1, value2)
説明
2 つの STRING
値または BYTES
値を取ります。value1
内で最初に value2
が出現する位置のインデックス(開始値 1)を返します。value2
が見つからない場合は 0
を返します。
戻り値の型
INT64
例
WITH email_addresses AS
(SELECT
'foo@example.com' AS email_address
UNION ALL
SELECT
'foobar@example.com' AS email_address
UNION ALL
SELECT
'foobarbaz@example.com' AS email_address
UNION ALL
SELECT
'quxexample.com' AS email_address)
SELECT
STRPOS(email_address, '@') AS example
FROM email_addresses;
+---------+
| example |
+---------+
| 4 |
| 7 |
| 10 |
| 0 |
+---------+
SUBSTR
SUBSTR(value, position[, length])
説明
指定された STRING
値または BYTES
値の部分文字列を返します。
position
引数は、部分文字列の開始位置を指定する整数であり、position = 1 は最初の文字またはバイトを指します。position
が負の値である場合、value
の最後からカウントします(-1 は最後の文字を示す)。position
がゼロまたは -LENGTH(value)
より小さい場合、部分文字列は position = 1 から始まります。
length
引数は、value
が STRING
の場合に返される最大文字数、または value
が BYTES
の場合に返されるバイト数を指定します。length
が 0 より小さい場合は、関数によってエラーが表示されます。返される部分文字列は length
より短くなる場合があります。たとえば、length
が value
の長さを超える場合、または部分文字列の開始位置と length
の合計が value
よりも長い場合が該当します。
戻り値の型
STRING
または BYTES
例
WITH items AS
(SELECT 'apple' as item
UNION ALL
SELECT 'banana' as item
UNION ALL
SELECT 'orange' as item)
SELECT
SUBSTR(item, 2) as example
FROM items;
+---------+
| example |
+---------+
| pple |
| anana |
| range |
+---------+
WITH items AS
(SELECT 'apple' as item
UNION ALL
SELECT 'banana' as item
UNION ALL
SELECT 'orange' as item)
SELECT
SUBSTR(item, 2, 2) as example
FROM items;
+---------+
| example |
+---------+
| pp |
| an |
| ra |
+---------+
WITH items AS
(SELECT 'apple' as item
UNION ALL
SELECT 'banana' as item
UNION ALL
SELECT 'orange' as item)
SELECT
SUBSTR(item, -2) as example
FROM items;
+---------+
| example |
+---------+
| le |
| na |
| ge |
+---------+
WITH items AS
(SELECT 'apple' as item
UNION ALL
SELECT 'banana' as item
UNION ALL
SELECT 'orange' as item)
SELECT
SUBSTR(item, 1, 123) as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
+---------+
WITH items AS
(SELECT 'apple' as item
UNION ALL
SELECT 'banana' as item
UNION ALL
SELECT 'orange' as item)
SELECT
SUBSTR(item, 123) as example
FROM items;
+---------+
| example |
+---------+
| |
| |
| |
+---------+
WITH items AS
(SELECT 'apple' as item
UNION ALL
SELECT 'banana' as item
UNION ALL
SELECT 'orange' as item)
SELECT
SUBSTR(item, 123, 5) as example
FROM items;
+---------+
| example |
+---------+
| |
| |
| |
+---------+
SUBSTRING
SUBSTRING(value, position[, length])
SUBSTR
のエイリアス。
TO_BASE32
TO_BASE32(bytes_expr)
説明
BYTES
のシーケンスを base32 でエンコードされた STRING
に変換します。base32 でエンコードされた STRING
を BYTES
に変換するには、FROM_BASE32 を使用します。
戻り値の型
STRING
例
SELECT TO_BASE32(b'abcde\xFF') AS base32_string;
+------------------+
| base32_string |
+------------------+
| MFRGGZDF74====== |
+------------------+
TO_BASE64
TO_BASE64(bytes_expr)
説明
BYTES
のシーケンスを base64 でエンコードされた STRING
に変換します。base64 でエンコードされた STRING
を BYTES
に変換するには、FROM_BASE64 を使用します。
いくつかの base64 エンコーディングが使用されています。これは、64 桁とパディングのエンコードに、65 個の ASCII 文字の中のどのアルファベットが使用されるかによって異なります。詳しくは、RFC 4648 をご覧ください。この関数はパディングを追加し、アルファベット [A-Za-z0-9+/=]
を使用します。
戻り値の型
STRING
例
SELECT TO_BASE64(b'\377\340') AS base64_string;
+---------------+
| base64_string |
+---------------+
| /+A= |
+---------------+
別の base64 アルファベットによるエンコードを使用するには、REPLACE
関数を含む TO_BASE64
の作成が必要になる場合があります。たとえば、ウェブ プログラミングで一般的に使用されている URL セーフおよびファイル名セーフ型の base64url
エンコードでは、最後の文字として +/=
ではなく -_=
を使用します。base64url
でエンコードされた文字列をエンコードするには、+
と /
をそれぞれ -
と _
に置き換えます。
SELECT REPLACE(REPLACE(TO_BASE64(b'\377\340'), '+', '-'), '/', '_') as websafe_base64;
+----------------+
| websafe_base64 |
+----------------+
| _-A= |
+----------------+
TO_CODE_POINTS
TO_CODE_POINTS(value)
説明
value を取り、INT64
の配列を返します。
value
がSTRING
の場合、返される配列の各要素はコードポイントを表します。各コードポイントは、[0, 0xD7FF] から [0xE000, 0x10FFFF] の範囲になります。value
がBYTES
の場合、配列内の各要素は拡張 ASCII 文字の値で、範囲は [0, 255] です。
コードポイントの配列を STRING
または BYTES
に変換するには、CODE_POINTS_TO_STRING または CODE_POINTS_TO_BYTES をご覧ください。
戻り値の型
ARRAY
/INT64
例
次の例では、文字配列内の各要素についてコードポイントを取得します。
SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;
+---------+------------------------------------+
| word | code_points |
+---------+------------------------------------+
| foo | [102, 111, 111] |
| bar | [98, 97, 114] |
| baz | [98, 97, 122] |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama | [108, 108, 97, 109, 97] |
+---------+------------------------------------+
次の例では、BYTES
の整数表現を対応する ASCII 文字値に変換しています。
SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;
+------------------+------------------------+
| word | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255] |
| foo | [102, 111, 111] |
+------------------+------------------------+
次の例では、BYTES
の結果と STRING
の結果の違いを示しています。
SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;
+------------+----------+
| b_result | s_result |
+------------+----------+
| [196, 128] | [256] |
+------------+----------+
文字 Ā が 2 バイトの Unicode シーケンスで表されることにご注意ください。その結果、BYTES
バージョンの TO_CODE_POINTS
は 2 つの要素からなる配列を返し、STRING
バージョンは 1 つの要素からなる配列を返します。
TO_HEX
TO_HEX(bytes)
説明
BYTES
のシーケンスを 16 進数の STRING
に変換します。STRING
内の各バイトを範囲 (0..9, a..f)
内の 2 つの 16 進数文字に変換します。16 進数でエンコードされた STRING
を BYTES
に変換するには、FROM_HEX を使用します。
戻り値の型
STRING
例
WITH Input AS (
SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;
+----------------------------------+------------------+
| byte_string | hex_string |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar | 666f6f626172 |
+----------------------------------+------------------+
TRANSLATE
TRANSLATE(expression, source_characters, target_characters)
説明
expression
で、source_characters
内の各文字を target_characters
内の対応する文字に置き換えます。すべての入力は同じ型(STRING
または BYTES
)にする必要があります。
expression
内の各文字を 1 回変換します。expression
でsource_characters
にない文字はexpression
で変更されません。source_characters
でtarget_characters
に対応する文字がない文字は結果から除外されます。source_characters
で重複する文字があると、エラーが発生します。
戻り値の型
STRING
または BYTES
例
WITH example AS (
SELECT 'This is a cookie' AS expression, 'sco' AS source_characters, 'zku' AS
target_characters UNION ALL
SELECT 'A coaster' AS expression, 'co' AS source_characters, 'k' as
target_characters
)
SELECT expression, source_characters, target_characters, TRANSLATE(expression,
source_characters, target_characters) AS translate
FROM example;
+------------------+-------------------+-------------------+------------------+
| expression | source_characters | target_characters | translate |
+------------------+-------------------+-------------------+------------------+
| This is a cookie | sco | zku | Thiz iz a kuukie |
| A coaster | co | k | A kaster |
+------------------+-------------------+-------------------+------------------+
TRIM
TRIM(value_to_trim[, set_of_characters_to_remove])
Description
トリミングする STRING
値または BYTES
値を取ります。
トリミングする値が STRING
の場合は、この値から set_of_characters_to_remove
の先頭と末尾の Unicode コードポイントがすべて削除されます。コードポイントのセットは省略可能です。指定しない場合は、トリミングする値の先頭と末尾からすべての空白文字が削除されます。
トリミングする値が BYTES
の場合は、この値から set_of_characters_to_remove
の先頭と末尾のバイトがすべて削除されます。バイトのセットは必須です。
戻り値の型
value_to_trim
がSTRING
値である場合は、STRING
。value_to_trim
がBYTES
値である場合は、BYTES
。
例
次の例では、set_of_characters_to_remove
が指定されていないため、すべての先頭と末尾の空白文字が item
から削除されます。
WITH items AS
(SELECT ' apple ' as item
UNION ALL
SELECT ' banana ' as item
UNION ALL
SELECT ' orange ' as item)
SELECT
CONCAT('#', TRIM(item), '#') as example
FROM items;
+----------+
| example |
+----------+
| #apple# |
| #banana# |
| #orange# |
+----------+
次の例では、先頭と末尾の *
文字がすべて item
から削除されます。
WITH items AS
(SELECT '***apple***' as item
UNION ALL
SELECT '***banana***' as item
UNION ALL
SELECT '***orange***' as item)
SELECT
TRIM(item, '*') as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
+---------+
次の例では、先頭、末尾の x
、y
、z
の文字がすべて item
から削除されます。
WITH items AS
(SELECT 'xxxapplexxx' as item
UNION ALL
SELECT 'yyybananayyy' as item
UNION ALL
SELECT 'zzzorangezzz' as item
UNION ALL
SELECT 'xyzpearxyz' as item)
SELECT
TRIM(item, 'xyz') as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
次の例では、TRIM
が文字を Unicode コードポイントとして解釈する仕組みについて説明します。末尾の文字セットに特定の文字の結合記号が含まれている場合、TRIM
は同じ文字を別の文字から除去することを必要とする可能性があります。
SELECT
TRIM('abaW̊', 'Y̊') AS a,
TRIM('W̊aba', 'Y̊') AS b,
TRIM('abaŪ̊', 'Y̊') AS c,
TRIM('Ū̊aba', 'Y̊') AS d;
+---------------------------+
| a | b | c | d |
+---------------------------+
| abaW | W̊aba | abaŪ | Ūaba |
+---------------------------+
次の例では、先頭、末尾の b'n'
、b'a'
、b'\xab'
のバイトがすべて item
から削除されます。
WITH items AS
(
SELECT b'apple' as item UNION ALL
SELECT b'banana' as item UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as item
)
SELECT item, TRIM(item, b'na\xab') AS examples
FROM items;
-- Note that the result of TRIM is of type BYTES, displayed as a base64-encoded string.
+----------------------+------------------+
| item | example |
+----------------------+------------------+
| YXBwbGU= | cHBsZQ== |
| YmFuYW5h | Yg== |
| q83vqrs= | ze+quw== |
+----------------------+------------------+
UNICODE
UNICODE(value)
説明
value
内の最初の文字の Unicode コードポイントを返します。value
が空の場合、または結果の Unicode コードポイントが 0
の場合は、0
を返します。
戻り値の型
INT64
例
SELECT UNICODE('âbcd') as A, UNICODE('â') as B, UNICODE('') as C, UNICODE(NULL) as D;
+-------+-------+-------+-------+
| A | B | C | D |
+-------+-------+-------+-------+
| 226 | 226 | 0 | NULL |
+-------+-------+-------+-------+
UPPER
UPPER(value)
説明
STRING
引数の場合は、元の文字列をすべて大文字の英字で返します。大文字と小文字の間のマッピングは、言語固有のマッピングを考慮せずに Unicode 文字データベースに従って行われます。
BYTES
引数の場合、引数は ASCII テキストとして処理され、127 を超えるすべてのバイトはそのまま残ります。
戻り値の型
STRING
または BYTES
例
WITH items AS
(SELECT
'foo' as item
UNION ALL
SELECT
'bar' as item
UNION ALL
SELECT
'baz' as item)
SELECT
UPPER(item) AS example
FROM items;
+---------+
| example |
+---------+
| FOO |
| BAR |
| BAZ |
+---------+