標準 SQL 中的字串函式

這些字串函式會處理兩種不同的值:STRING 與 BYTES 資料類型。STRING 值必須是格式正確的 UTF-8。

傳回位置值的函式 (例如 STRPOS),會將這些位置編碼為 INT64。1 值會參照第一個字元 (或位元組),2 會參照第二個,依此類推。0 值表示無效索引。處理 STRING 類型時,傳回的位置會參照字元位置。

所有字串比較都是依據位元組逐一完成,不考慮 Unicode 標準等價。

BYTE_LENGTH

BYTE_LENGTH(value)

說明

傳回的位元組長度,無論值的類型是 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 |
+------------+---------------------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

說明

取用延伸 ASCII 碼點的陣列 (INT64 的 ARRAY) 並傳回 BYTES。

如要將 BYTES 轉換成碼點的陣列,請參閱 TO_CODE_POINTS

傳回類型

BYTES

範例

以下是使用 CODE_POINTS_TO_BYTES 的基本範例。

SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;

+-------+
| bytes |
+-------+
| AbCd  |
+-------+

以下範例使用迴轉 13 位 (ROT13) 演算法來編碼字串。

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;

+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat!   |
+----------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

說明

取用 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ÿȁЀ   |
+--------+

以下範例會計算一組字詞中的字母頻率。

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[, ...])

說明

將一或多個串連成單一結果。

傳回類型

STRING 或 BYTES

範例


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         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

說明

取用兩個。如果第二個值是第一個值的後置字串,就換傳回 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

BigQuery 支援用來格式化字串的 FORMAT() 函式,這個函式與 C 的 printf 函式類似。它會從包含零或更多格式指定碼的格式字串產生出 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
DATE 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()) 來設定格式。 例如:

FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));

會傳回

date: January 02, 2015!

語法

FORMAT() 語法會取用格式字串與引數變數長度清單並產生 STRING 結果:

FORMAT(<format_string>, ...)

<format_string> 運算式可包含零或多個格式指定碼。 每個格式指定碼都由 % 符號引入,且必須對應至一或多個剩餘引數。在大多數情況下,除非 * 指定碼存在,否則都是一對一的對應。例如,%.*i 對應至兩個引數—長度引數和帶正負號整數引數。如果與格式指定碼相關的引數數目和引數數目不同,就會發生錯誤。

支援的格式指定碼

FORMAT() 函式格式指定碼遵循這個原型:

%[flags][width][.precision]specifier

下表中列出支援的格式指定碼。 從 printf() 擴充的項目會以斜體列出。

指定碼 說明 範例 類型
di 十進位整數 392 INT64
o 八進位 610
INT64*
x 十六進位整數 7fa
INT64*
X 十六進位整數 (大寫) 7FA
INT64*
f 十進位浮點,小寫 392.65
inf NaN
NUMERIC
FLOAT64
F 十進位浮點,大寫 392.65
inf NAN
NUMERIC
FLOAT64
e 科學記數法 (尾數/指數),小寫 3.9265e+2
inf NaN
NUMERIC
FLOAT64
E 科學記數法 (尾數/指數),大寫 3.9265E+2
inf NAN
NUMERIC
FLOAT64
g 使用最短表示法,%e 或 %f 392.65
FLOAT64
G 使用最短表示法,%E 或 %F 392.65
FLOAT64
s 字元字串 範例 STRING
t 傳回表示值的可列印字串。通常看起來類似將引數類型轉換為 STRING。請參閱以下的 %t 部分 範例
2014‑01‑01
<any>
T 產生的字串是有效的 BigQuery 常數,且常數的類型與值的類型類似 (可能更寬,或可能是字串)。請參閱以下的 %T 部分 'sample'
b'bytes sample'
1234
2.3
date '2014‑01‑01'
<any>
% 「%%」會產生單一的「%」 %

*如果使用負值,則 o、x 與 X 指定碼會產生錯誤。

格式指定碼可以選擇性地包含在指定碼原型上方列出的子指定碼。

而這些子指定碼必須符合下列規格。

旗標
旗標 說明
- 在指定欄位寬度內靠左對齊;預設為靠右對齊 (請參閱寬度子指定碼)
+ 強制在結果前面加上加號或減號 (+-),即使是正數也加上。預設只在負數前面加上 -
<space> 如果沒有正負號可以寫上,就會在值前面插入一個空格
# 與 o、x 或 X 指定碼搭配使用。分別為零以外的值在前面加上 0、0x 或 0X
0 當指定填補時,為數字靠左填補零 (0) 而非空格 (請參閱寬度子指定碼)
'

使用適當的分組字元來格式化整數。 例如:

  • FORMAT("%'d", 12345678) 傳回 12,345,678
  • FORMAT("%'x", 12345678) 傳回 bc:614e
  • FORMAT("%'o", 55555) 傳回 15,4403
  • 這個旗標只與十進位、十六進位與八進位值相關。

旗標可用任何順序指定。重複的旗標不算是錯誤。若旗標對某些元素類型不具相關性,系統就會將其忽略。

寬度
寬度 說明
<number> 要列印的字元數量下限。如果要列印的值短於這個數目,會為結果填補空格。即使結果較大,也不會截斷值
* 不會在格式字串中指定寬度,但會做為前置於必須格式化之引數前面的其他整數值引數
精確度
精確度 說明
.<number> 針對整數指定碼 (d、i、o、u、x、X):精確度指定了要編寫的位數下限。如果要編寫的值短於這個數目,就會為結果填補尾隨零。即使結果較長,也不會截斷值。精確度為 0 表示不會為值 0 編寫任何字元。對於 a、A、e、E、f 與 F 指定碼:這是要列印在小數點之後的位數 (預設是 6)
.* 不會在格式字串中指定精確度,但會做為前置於必須格式化之引數前面的其他整數值引數

%t 與 %T 行為

%t%T 格式指定碼是針對所有類型定義的。寬度、精確度,以及旗標的行為就像用於 %s 時一樣:width 是最小寬度,STRING 將會填補至該大小,而 precision 則是所要顯示內容的最大寬度,STRING 會在填補至寬度前,截斷自己與該大小相符。

%t 一定是值的可閱讀形式。

%T 一律是類似類型的有效 SQL 常值,例如較寬的數字類型。 除了非有限浮點值的特殊案例外,該常值將不會包含類型轉換或類型名稱。

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"
ARRAY [value, value, ...]
其中值的格式為 %t
[value, value, ...]
其中值的格式為 %T
STRUCT (value, value, ...)
其中欄位的格式為 %t
(value, value, ...)
其中欄位的格式為 %T 特殊案例: 零個欄位:STRUCT() 一個欄位:STRUCT(value)

錯誤狀況

如果格式指定碼無效,或與相關引數類型不相容,又或是提供了錯誤的數字或引數,就會產生錯誤。例如,以下的 <format_string> 運算式皆無效:

FORMAT('%s', 1)
FORMAT('%')

NULL 引數處理

NULL 格式字串會產生 輸出 STRING。在此情況下會忽略其他任何引數。

如果 NULL 引數存在,函式一般會產生 值。例如,FORMAT('%i', <NULL expression>) 會產生一個 NULL STRING 做為輸出。

但也有一些例外:如果格式指定碼是 %t 或 %T (這兩者都會產生與 CAST 和常值語意有效相符的 STRING),NULL 值會在結果 STRING 中產生「NULL」(不含引號)。例如,以下函式:

FORMAT('00-%t-00', <NULL expression>);

會傳回

00-NULL-00

其他語意規則

FLOAT64 值可以為 +/-infNaN。當引數具有這裡的其中一個值時,格式指定碼 %f%F%e%E%g%G%t 的結果依狀況會分別為 inf-infnan (大寫也一樣)。這與 BigQuery 將這些值的類型轉換為 STRING 的方式一致。對於 %T,BigQuery 會傳回 FLOAT64 值加上引號的字串,但這些值沒有非字串常值表示。

FROM_BASE32

FROM_BASE32(string_expr)

說明

將採 base32 編碼的輸入 string_expr 轉換成 BYTES 格式。如要將 BYTES 轉換成採用 Base64 編碼的字串,請使用 TO_BASE64()

傳回類型

BYTES

範例

SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;

+-----------+
| byte_data |
+-----------+
| abcde\xff |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

說明

將採 base64 編碼的輸入 string_expr 轉換為 BYTES 格式。如要將 BYTES 轉換成採用 Base64 編碼的 STRING,請使用 TO_BASE64()

傳回類型

BYTES

範例

SELECT FROM_BASE64('3q2+7w==') AS byte_data;

+------------------+
| byte_data        |
+------------------+
| \xde\xad\xbe\xef |
+------------------+

FROM_HEX

FROM_HEX(string)

說明

將採十六進位編碼的 STRING 轉換成 BYTES 格式。如果輸入的 STRING 包含 (0..9, A..F, a..f) 範圍以外的字元,則傳回錯誤。小寫字元沒有影響。如果輸入 STRING 的字元數為奇數,這個函式的行為會像是輸入有額外的前置 0。如要將 BYTES 轉換為採十六進位編碼的 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;
+------------------+----------------------------------+
| hex_str          | bytes_str                        |
+------------------+----------------------------------+
| 0AF              | \x00\xaf                         |
| 00010203aaeeefff | \x00\x01\x02\x03\xaa\xee\xef\xff |
| 666f6f626172     | foobar                           |
+------------------+----------------------------------+

LENGTH

LENGTH(value)

說明

傳回的長度。傳回值若是 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])

說明

傳回由 original_value 加上 pattern 做為前置字元的return_length 為 INT64,可指定傳回值的長度。如果 original_value 是 BYTES,return_length 便是位元組數。如果 original_value 是 STRING,return_length 便是字元數。

pattern 的預設值為空格。

original_valuepattern 必須是相同的資料類型。

如果 return_length 小於或等於 original_value 長度,這個函式會傳回 original_value 值,並將其截斷為 值的長度。舉例來說,LPAD("hello world", 7); 會傳回 "hello w"

如果 original_valuereturn_lengthpattern 為 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])

說明

取用 STRING (value),並以正規化字串傳回。

正規化可用來確保兩個字串相等。正規化通常用於兩個字串在螢幕上顯示結果相同但具有不同 Unicode 碼點的情況。

NORMALIZE 支援四種選用的正規化模式:

名稱 說明
NFC 正規化形式正式合成 按正式相等性分解及重組字元。
NFKC 正規化形式相容性合成 按相容性分解字元,然後按正式相等性重組字元。
NFD 正規化形式正式分解 按正式相等性分解字元,多個結合字元會以特定順序排列。
NFKD 正規化形式相容性分解 按相容性分解字元,多個結合字元會以特定順序排列。

預設的正規化模式為 NFC

傳回類型

STRING

範例

SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;

+---+---+------------+
| 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])

說明

取用 STRING (value),並執行與 NORMALIZE 相同的動作,以及不區分大小寫的全大/小寫轉換運算。

NORMALIZE_AND_CASEFOLD 支援四種選用的正規化模式:

名稱 說明
NFC 正規化形式正式合成 按正式相等性分解及重組字元。
NFKC 正規化形式相容性合成 按相容性分解字元,然後按正式相等性重組字元。
NFD 正規化形式正式分解 按正式相等性分解字元,多個結合字元會以特定順序排列。
NFKD 正規化形式相容性分解 按相容性分解字元,多個結合字元會以特定順序排列。

預設的正規化模式為 NFC

傳回類型

STRING

範例

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 |
+---+----+-------+-------+------+------+

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regex)

說明

如果 value 與規則運算式 regex 部分相符,就會傳回 TRUE。

如果 regex 引數無效,函式會傳回錯誤。

您可以使用 ^ (文字的開頭) 與 $ (文字的結尾) 來搜尋完全相符的情況。基於規則運算式運算子的優先順序,用圓括號包住 ^$ 之間的所有文字是不錯的做法。

傳回類型

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, regex)

說明

傳回 value 中與規則運算式 regex 相符的第一個子字串。如果沒有相符項目,就傳回 NULL

如果規則運算式中包含擷取群組,函式會傳回與該擷取群組相符的子字串。如果運算式不包含擷取群組,函式會傳回整個相符子字串。

在以下情況下會傳回錯誤:

  • 規則運算式無效
  • 規則運算式有超過一個的擷取群組

傳回類型

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              |
+------------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regex)

說明

傳回與規則運算式 regex 相符的 value 之所有子字串的陣列。

REGEXP_EXTRACT_ALL 函式只會傳回非重疊相符項目。例如,使用此函式從 banana 中擷取 ana 只會傳回一個子字串,而不是兩個字子串。

傳回類型

STRING 或 BYTES 的陣列

範例


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_REPLACE

REGEXP_REPLACE(value, regex, replacement)

說明

傳回 STRING,其中所有符合規則運算式 regexvalue 的子字串都會被替換成 replacement

您可以在 replacement 引數內使用反斜線逸出數字 (\1 到 \9),好在 regex 模式中插入與對應放入括號內群組相符的文字。請使用 \0 來參照完整的相符文字。

REGEXP_REPLACE 函式只會取代非重疊相符項目。例如,取代 banana 內的 ana 只會造成一次取代,而不是兩個。

如果 regex 引數不是有效的規則運算式,這個函式會傳回錯誤。

傳回類型

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> |
+--------------------------+

REPLACE

REPLACE(original_value, from_value, to_value)

說明

to_value 替換所有出現在 original_value 中的 from_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 重複構成的repetitions 參數指定 original_value 要重複的次數。如果 original_valuerepetitions 為 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           |
+---------------+----------------+--------------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

說明

傳回由 original_value 後面附加 pattern 所構成的return_length 為 INT64,可指定傳回值的長度。如果 original_value 是 BYTES,return_length 便是位元組數。如果 original_value 是 STRING,return_length 便是字元數。

pattern 的預設值為空格。

original_valuepattern 必須是相同的資料類型。

如果 return_length 小於或等於 original_value 長度,這個函式會傳回 original_value 值,並將其截斷為 值的長度。舉例來說,RPAD("hello world", 7); 會傳回 "hello w"

如果 original_valuereturn_lengthpattern 為 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)

說明

將一串位元組序列轉換為字串。任何無效的 UTF-8 字元都會被 Unicode 取代字元 U+FFFD 給取代。

傳回類型

STRING

範例

以下陳述式會傳回 Unicode 取代字元 �。

SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;

SPLIT

SPLIT(value[, delimiter])

說明

使用 delimiter 引數分割 value

對於 STRING,預設的分隔符號是逗號 ,

對於 BYTES,您必須指定分隔符號。

以空的分隔符號分割時,STRING 值會產生 UTF-8 字元的陣列,BYTES 值則會產生 BYTES 的陣列。

而分割空的 STRING 會傳回帶有單一空白 STRING 的 ARRAY。

傳回類型

STRUNG 類型的 ARRAY 或 BYTES 類型的 ARRAY

範例


WITH letters AS
  (SELECT "a b c d" as letter_group
  UNION ALL
  SELECT "e f g h" as letter_group
  UNION ALL
  SELECT "i j k l" as letter_group)

SELECT SPLIT(letter_group, " ") as example
FROM letters;

+----------------------+
| example              |
+----------------------+
| [a, b, c, d]         |
| [e, f, g, h]         |
| [i, j, k, l]         |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

說明

取用兩個。如果第二個值是第一個值的前置字元,就會傳回 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(string, substring)

說明

傳回在 string 內第一次出現的 substring 的索引值,而此索引值是以 1 起算。若沒找到 substring,則傳回 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])

說明

傳回提供之的子字串。position 引數是指定子字串開始位置的整數,position = 1 表示第一個字元或位元組。length 引數是 STRING 引數的字元數上限,或 BYTES 引數的位元組上限。

如果 position 是負數,函式會從 value 的結尾開始計數,-1 代表最後一個字元。

如果 position 是 STRING 左端以外位置 ( = 0 或 < -LENGTH(value)),函式會從 position = 1 開始。如果 length 超過 value 的長度,就會傳回少於 length 的字元。

如果 length 小於 0,函式會傳回錯誤。

傳回類型

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      |
+---------+

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

傳回類型

STRING

範例

SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;

+---------------+
| base64_string |
+---------------+
| 3q2+7w==      |
+---------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

說明

取用並傳回 INT64 的陣列。

  • 如果 value 是 STRING,則傳回陣列中的每個元素都代表一個碼點。每個碼點都在 [0, 0xD7FF] 與 [0xE000, 0x10FFFF] 的範圍之內。
  • 如果 value 是 BYTES,陣列中的每個元素都是 [0, 255] 範圍中的延伸 ASCII 字元值。

如要將碼點陣列轉換成 STRING 或 BYTES,請參閱 CODE_POINTS_TO_STRINGCODE_POINTS_TO_BYTES

傳回類型

INT64 的 ARRAY

範例

以下範例在字詞的陣列中取得每個元素的碼點。

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]    |
+------------+----------+

請注意,字元 Ā 由雙位元組的 Unicode 序列表示。因此,BYTES 版本的 TO_CODE_POINTS 傳回了含有兩個元素的陣列,而 STRING 版本傳回了含有單一元素的陣列。

TO_HEX

TO_HEX(bytes)

說明

將一串 BYTES 序列轉換為十六進位的 STRING。將 STRING 中的每個位元組轉換成 (0..9, a..f) 範圍內的兩個十六進位字元。如要將採十六進位編碼的 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       |
+----------------------------------+------------------+
| foobar                           | 666f6f626172     |
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

說明

移除符合 value2 的所有前置與尾隨字元。若未指定 value2,會移除所有前置與尾隨空格字元 (如 Unicode 標準的定義)。如果第一個引數的類型是 BYTES,就需要第二個引數。

如果 value2 含有超過一個的字元或位元組,函式會移除 中包含的所有前置或尾隨字元或位元組。

傳回類型

STRING 或 BYTES

範例


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# |
+----------+

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  |
+---------+

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    |
+---------+

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     |
+---------+