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

以下示例使用 rotate-by-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", 10) 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 字符串 sample STRING
t 返回表示值的可打印字符串。通常看起来类似于将参数类型转换为 STRING。请参阅下面的 %t 部分 sample
2014‑01‑01
<任意>
T 生成一个字符串,这是一个类型与值类型相似的有效 BigQuery 常量(可能宽度更大,也可能是字符串)。 请参阅下面的 %T 部分 'sample'
b'bytes sample'
1234
2.3
date '2014‑01‑01'
<任意>
% '%%' 会生成一个 '%' % 不适用

*如果使用了负值,则说明符 o、x 和 X 会引发错误。

格式说明符可以选择性地在说明符原型中包含上面所述的子说明符。

这些子说明符必须符合以下规范。

标记
标记 说明
- 在给定字段宽度内左对齐;默认设置为右对齐(请参阅宽度子说明符)
+ 强制在结果前面加上加号或减号(+-),即便对于正数也是如此。默认情况下,仅有负数带 - 符号前缀
<空格> 如果不会写入任何符号,则在值前插入一个空格
# 配合 o、x 或 X 说明符使用。对于 0 以外的值,在值前分别加上 0、0x 或 0X 前缀
0 在指定填充时,在左侧填充数字零 (0),而非填充空格(参见宽度子说明符)
'

使用适当的分组字符格式化整数。例如:

  • FORMAT("%'d", 12345678) 返回 12,345,678
  • FORMAT("%'x", 12345678) 返回 bc:614e
  • FORMAT("%'o", 55555) 返回 15,4403
  • 此标记仅与十进制、十六进制和八进制值相关。

标记可以按照任意顺序指定。标记重复不属于错误。在标记与某些元素类型不相关时,标记会被忽略。

宽度
宽度 说明
<数字> 要打印的最小字符数。如果要打印的值比这个数字短,结果将用空格填充。即便结果较大,该值也不会被截断
* 宽度并非在格式字符串中指定,而是作为一个额外的整数值参数指定,置于必须格式化的参数之前
精度
精度 说明
.<数字> 对于正数说明符(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 [值, 值, ...]
其中的值使用 %t 设置格式
[值, 值, ...]
其中的值使用 %T 设置格式
STRUCT (值, 值, ...)
其中的字段使用 %t 设置格式
(值, 值, ...)
其中的字段使用 %T 设置格式

特殊情况:
零个字段:STRUCT()
一个字段:STRUCT(value)

错误条件

如果格式说明符无效,或者与相关参数类型不兼容,或者提供了错误的数字或参数,则会引发错误。例如,以下 <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 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)

说明

返回 value 中与正则表达式 regex 匹配的所有子字符串的数组。

REGEXP_EXTRACT_ALL 函数仅返回非重叠的匹配项。例如,使用此函数从 ana 中提取 banana 时仅返回一个子字符串,而不是两个。

返回类型

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_REPLACE

REGEXP_REPLACE(value, regex, replacement)

说明

value 中与正则表达式 regex 匹配的所有子字符串都替换为 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)

说明

from_value 中出现的 to_value 全部替换为 original_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])

说明

返回由附带前缀 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 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。

返回类型

由 STRING 类型组成的 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 左端(position = 0 或 position < -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 序列。因此,TO_CODE_POINTS 的 BYTES 版本会返回包含两个元素的数组,而 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     |
+---------+
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面