標準 SQL 中的 String 函式

這些 string 函式會處理兩種不同的值: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" 作為 <type>)
CAST("-inf" 作為 <type>)
CAST("nan" 作為 <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 [值, 值, ...]
其中值的格式為 %t
[值, 值, ...]
其中值的格式為 %T
STRUCT (值, 值, ...)
其中欄位的格式為 %t
(值, 值, ...)
其中欄位的格式為 %T

特殊案例:
零個欄位:STRUCT()
一個欄位:STRUCT(值)

錯誤狀況

如果格式指定碼無效,或與相關引數類型不相容,或提供了錯誤數字或引數,就會產生錯誤。例如,以下 <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 值可以是 +/-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 轉換成採 base32 編碼的 STRING,請使用 TO_BASE32

傳回類型

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

說明

傳回的是由開頭帶有 patternoriginal_value 所構成。return_length 是指定傳回值長度的 INT64。如果 original_value 是 BYTES,return_length 就是位元組數。如果 original_value 是 STRING,return_length 就是字元數。

pattern 的預設值是空格。

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

如果 return_length 小於或等於 original_value 長度,這個函式會傳回 original_value 值,截斷至 return_length 的值。例如,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 模式 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 模式 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)

說明

傳回與規則運算式 regex 相符之 value 的所有子字串都用 replacement 取代的 STRING。

您可以在 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)

說明

在 original_value 中以 to_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 重複
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])

說明

傳回的由結尾帶有 patternoriginal_value 所構成。return_length 是指定傳回值長度的 INT64。如果 original_value 是 BYTES,return_length 就是位元組數。如果 original_value 是 STRING,return_length 就是字元數。

pattern 的預設值是空格。

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

如果 return_length 小於或等於 original_value 長度,這個函式會傳回 original_value 值,截斷至 return_length 的值。例如,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 模式 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 模式 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 引數是指定子字串開始位置的整數,位置 = 1 指示第一個字元或位元組。length 引數是 STRING 引數的字元數上限,或 BYTES 引數的位元組上限。

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

如果 position 是 STRING 左端以外的位置 (position = 0 或 position < -LENGTH(value)),函式會從位置 = 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 中包含一個以上的字元或位元組,函式會移除 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     |
+---------+
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁