标准 SQL 中的字符串函数

这些字符串函数作用于两种不同的值:STRINGBYTES 数据类型。STRING 值必须采用正确的 UTF-8 格式。

返回位置值的函数(如 STRPOS)会将这些位置编码为 INT64 类型。值 1 表示第一个字符(或字节),2 表示第二个字符(或字节),以此类推。值 0 表示无效索引。处理 STRING 类型时,返回的位置表示字符位置。

按字节逐一比较所有字符串,而不考虑 Unicode 规范相等性。

ASCII

ASCII(value)

说明

返回 value 中第一个字符或字节的 ASCII 代码。如果 value 为空或者第一个字符或字节的 ASCII 代码为 0,则返回 0

返回类型

INT64

示例

SELECT ASCII('abcd') as A, ASCII('a') as B, ASCII('') as C, ASCII(NULL) as D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| 97    | 97    | 0     | NULL  |
+-------+-------+-------+-------+

BYTE_LENGTH

BYTE_LENGTH(value)

说明

无论值的类型是 STRING 还是 BYTES,该函数都会返回 STRINGBYTES 值的长度(以 BYTES 为单位)。

返回类型

INT64

示例

WITH example AS
  (SELECT "абвгд" AS characters, b"абвгд" AS bytes)

SELECT
  characters,
  BYTE_LENGTH(characters) AS string_example,
  bytes,
  BYTE_LENGTH(bytes) AS bytes_example
FROM example;

+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд      | 10             | абвгд | 10            |
+------------+----------------+-------+---------------+

CHAR_LENGTH

CHAR_LENGTH(value)

说明

返回 STRING 的长度(以字符为单位)。

返回类型

INT64

示例

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHAR_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

说明

相当于 CHAR_LENGTH

返回类型

INT64

示例

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHR

CHR(value)

说明

接受 Unicode 代码点并返回与代码点匹配的字符。每个有效的代码点都应位于 [0, 0xD7FF] 和 [0xE000, 0x10FFFF] 范围内。如果代码点为 0,则返回空字符串。如果指定了无效的 Unicode 代码点,则系统会返回错误。

如需使用 Unicode 代码点数组,请参阅 CODE_POINTS_TO_STRING

返回类型

STRING

示例

SELECT CHR(65) AS A, CHR(255) AS B, CHR(513) AS C, CHR(1024)  AS D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| A     | ÿ     | ȁ     | Ѐ     |
+-------+-------+-------+-------+
SELECT CHR(97) AS A, CHR(0xF9B5) AS B, CHR(0) AS C, CHR(NULL) AS D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| a     | 例    |       | NULL  |
+-------+-------+-------+-------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_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;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'AbCd' is the result.
+----------+
| bytes    |
+----------+
| QWJDZA== |
+----------+

以下示例使用回转 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;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'Grfg Fgevat!' is the result.
+------------------+
| encoded_string   |
+------------------+
| R3JmZyBGZ2V2YXQh |
+------------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

说明

接受 Unicode 码位的数组(由 INT64 类型值组成的 ARRAY),并返回一个 STRING。如果码位为 0,则不会在 STRING 中为其返回字符。

如需从字符串转换为码位数组,请参阅 TO_CODE_POINTS

返回类型

STRING

示例

以下是使用 CODE_POINTS_TO_STRING 的基本示例。

SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+
SELECT CODE_POINTS_TO_STRING([97, 0, 0xF9B5]) AS string;

+--------+
| string |
+--------+
| a例    |
+--------+
SELECT CODE_POINTS_TO_STRING([65, 255, NULL, 1024]) AS string;

+--------+
| string |
+--------+
| NULL   |
+--------+

以下示例会计算一组单词中字母的出现频率。

WITH Words AS (
  SELECT word
  FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM Words,
  UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;

+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a      | 5            |
| f      | 3            |
| r      | 2            |
| b      | 2            |
| l      | 2            |
| o      | 2            |
| g      | 1            |
| z      | 1            |
| e      | 1            |
| m      | 1            |
| i      | 1            |
+--------+--------------+

CONCAT

CONCAT(value1[, ...])

说明

将一个或多个串联成一个结果。所有值都必须为 BYTES 或可转换为 STRING 的数据类型。

如果任何输入参数为 NULL,则该函数返回 NULL

返回类型

STRINGBYTES

示例

SELECT CONCAT("T.P.", " ", "Bar") as author;

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+
SELECT CONCAT("Summer", " ", 1923) as release_date;

+---------------------+
| release_date        |
+---------------------+
| Summer 1923         |
+---------------------+

With Employees AS
  (SELECT
    "John" AS first_name,
    "Doe" AS last_name
  UNION ALL
  SELECT
    "Jane" AS first_name,
    "Smith" AS last_name
  UNION ALL
  SELECT
    "Joe" AS first_name,
    "Jackson" AS last_name)

SELECT
  CONCAT(first_name, " ", last_name)
  AS full_name
FROM Employees;

+---------------------+
| full_name           |
+---------------------+
| John Doe            |
| Jane Smith          |
| Joe Jackson         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

说明

获取两个 STRINGBYTES 值。如果第二个值是第一个值的后缀,则返回 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())对其进行格式设置。例如:

SELECT 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.650000
inf
nan
NUMERIC
BIGNUMERIC
FLOAT64
F 十进制记数法,[-](整数部分).(小数部分)表示有限值,大写字母表示非有限值 392.650000
INF
NAN
NUMERIC
BIGNUMERIC
FLOAT64
e 科学记数法(尾数/指数)(小写) 3.926500e+02
inf
nan
NUMERIC
BIGNUMERIC
FLOAT64
E 科学记数法(尾数/指数)(大写) 3.926500E+02
INF
NAN
NUMERIC
BIGNUMERIC
FLOAT64
g 十进制记数法或科学记数法,具体取决于输入值的指数和指定的精度。小写。 如需了解详情,请参阅 %g 和 %G 行为 392.65
3.9265e+07
inf
nan
NUMERIC
BIGNUMERIC
FLOAT64
G 十进制记数法或科学记数法,具体取决于输入值的指数和指定的精度。大写。 如需了解详情,请参阅 %g 和 %G 行为 392.65
3.9265E+07
INF
NAN
NUMERIC
BIGNUMERIC
FLOAT64
s 字符串 sample STRING
t 返回表示值的可打印字符串。通常看起来类似于将参数类型转换为 STRING。 请参阅 %t 和 %T 行为 sample
2014‑01‑01
<任意>
T 生成一个字符串,该字符串是一个有效的 BigQuery 常量,其类型与值类型类似(可能宽度更大,也可能是字符串)。请参阅 %t 和 %T 行为 'sample'
b'bytes sample'
1234
2.3
date '2014‑01‑01'
<任意>
% '%%' 会生成一个 '%' % 不适用

*如果使用了负值,则说明符 %o%x%X 会抛出错误。

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

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

标记
标记 说明
- 在指定字段宽度内左对齐;默认设置为右对齐(请参阅宽度子说明符)
+ 强制在结果前面加上加号或减号(+-),即使是正数也一样。默认情况下,只有负数才会带 - 符号前缀
<空格> 如果不会写入任何符号,则在值前插入一个空格
#
  • 对于“%o”、“%x”和“%X”,此标志表示,对于 0 以外的值,在值前分别加上 0、0x 或 0X 前缀。
  • 对于“%f”、“%F”、“%e”和“%E”,此标志表示即使没有小数部分,也要添加小数点,除非该值为非有限值。
  • 对于“%g”和“%G”,此标志表示即使没有小数部分,也要添加小数点(除非该值为非有限值),并且绝不移除小数点后的尾随零。
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。
  • 对于说明符“%g”和“%G”,这是在移除小数点后的尾随零之前要输出的有效位数。默认值为 6。
.* 精度并非在格式字符串中指定,而是作为一个额外的整数值参数,在需要设置格式的参数前面指定

%g 和 %G 行为

%g%G 格式说明符采用十进制记数法(如 %f%F 说明符)或科学记数法(如 %e%E 说明符),具体取决于输入值的指数和指定的精度

p 代表指定的精度(默认值为 6;如果指定的精度小于 1,则默认为 1)。首先将输入值转换为精度为 (p - 1) 的科学记数法。如果得到的指数部分 x 小于 -4 或不小于 p,则使用精度为 (p - 1) 的科学记数法;否则,使用精度为 (p - 1 - x) 的十进制记数法。

除非存在 # 标志,否则移除小数点后的尾随零;如果小数点后面没有数字,则也会将其移除。

%t 和 %T 行为

您需要为所有类型定义 %t%T 格式说明符。宽度精度标志的作用与其在 %s 中的作用相同:宽度是最小宽度,STRING 将填充到此宽度,精度是要显示的最大内容宽度,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 格式字符串是一个 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;

-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/  |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

说明

将 base64 编码的输入 string_expr 转换为 BYTES 格式。如需将 BYTES 转换为 base64 编码的 STRING,请使用 TO_BASE64

有几种常用的 base64 编码,它们的区别在于使用 65 个 ASCII 字符的字母来编码 64 位数字和内边距。如需了解详情,请参阅 RFC 4648。此函数要求字母 [A-Za-z0-9+/=]

返回类型

BYTES

示例

SELECT FROM_BASE64('/+A=') AS byte_data;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| /+A=      |
+-----------+

如需使用其他 base64 字符进行编码,您可能需要使用 REPLACE 函数编写 FROM_BASE64。例如,Web 编程中常用的 base64url 网址安全和文件名安全编码方法使用 -_= 作为最后的字符,而不是 +/=。如需对 base64url 编码的字符串进行解码,请将 +/ 分别替换为 -_

SELECT FROM_BASE64(REPLACE(REPLACE("_-A=", "-", "+"), "_", "/")) AS binary;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+--------+
| binary |
+--------+
| /+A=   |
+--------+

FROM_HEX

FROM_HEX(string)

说明

将十六进制编码的 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;

-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str          | bytes_str    |
+------------------+--------------+
| 0AF              | AAECA6ru7/8= |
| 00010203aaeeefff | AK8=         |
| 666f6f626172     | Zm9vYmFy     |
+------------------+--------------+

INITCAP

INITCAP(value[, delimiters])

说明

接受 STRING 并按以下格式返回它:每个单词的第一个字符大写,所有其他字符均小写。非字母字符保持不变。

delimiters 是可选的字符串参数,用于替换分隔字词的默认字符集。如果未指定 delimiters,则默认为以下字符:
<whitespace> [ ] ( ) { } / | \ < > ! ? @ " ^ # $ & ~ _ , . : ; * % + -

如果 valuedelimitersNULL,则该函数会返回 NULL

返回类型

STRING

示例

WITH example AS
(
  SELECT "Hello World-everyone!" AS value UNION ALL
  SELECT "tHe dog BARKS loudly+friendly" AS value UNION ALL
  SELECT "apples&oranges;&pears" AS value UNION ALL
  SELECT "καθίσματα ταινιών" AS value
)
SELECT value, INITCAP(value) AS initcap_value FROM example

+-------------------------------+-------------------------------+
| value                         | initcap_value                 |
+-------------------------------+-------------------------------+
| Hello World-everyone!         | Hello World-Everyone!         |
| tHe dog BARKS loudly+friendly | The Dog Barks Loudly+Friendly |
| apples&oranges;&pears         | Apples&Oranges;&Pears         |
| καθίσματα ταινιών             | Καθίσματα Ταινιών             |
+-------------------------------+-------------------------------+

WITH example AS
(
  SELECT "hello WORLD!" AS value, "" AS delimiters UNION ALL
  SELECT "καθίσματα ταιντιώ@ν" AS value, "τ@" AS delimiters UNION ALL
  SELECT "Apples1oranges2pears" AS value, "12" AS delimiters UNION ALL
  SELECT "tHisEisEaESentence" AS value, "E" AS delimiters
)
SELECT value, delimiters, INITCAP(value, delimiters) AS initcap_value FROM example;

+----------------------+------------+----------------------+
| value                | delimiters | initcap_value        |
+----------------------+------------+----------------------+
| hello WORLD!         |            | Hello world!         |
| καθίσματα ταιντιώ@ν  | τ@         | ΚαθίσματΑ τΑιντΙώ@Ν  |
| Apples1oranges2pears | 12         | Apples1Oranges2Pears |
| tHisEisEaESentence   | E          | ThisEIsEAESentence   |
+----------------------+------------+----------------------+

INSTR

INSTR(source_value, search_value[, position[, occurrence]])

说明

返回 source_valuesearch_value 的最小索引值(从 1 开始)。如果找不到匹配项,则返回 0。source_valuesearch_value 必须是同一类型(STRINGBYTES)。

如果指定了 position,则搜索会从 source_value 中的此位置开始,否则会从 source_value 的开头开始。如果 position 为负数,则该函数会从 source_value 末尾反向搜索,其中 -1 表示最后一个字符。position 不能为 0。

如果指定了 occurrence,则搜索会返回 source_valuesearch_value 的特定实例的位置,否则会返回第一次出现的索引。如果 occurrence 大于找到的匹配数,则返回 0。如果 occurrence > 1,则该函数会搜索重叠的出现项,换句话说,该函数会从上一个出现项中第二个字符开始搜索额外的出现项。occurrence 不能为 0 或负数。

返回类型

INT64

示例

WITH example AS
(SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 2 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 3 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'ann' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 2 as
occurrence
)
SELECT source_value, search_value, position, occurrence, INSTR(source_value,
search_value, position, occurrence) AS instr
FROM example;

+--------------+--------------+----------+------------+-------+
| source_value | search_value | position | occurrence | instr |
+--------------+--------------+----------+------------+-------+
| banana       | an           | 1        | 1          | 2     |
| banana       | an           | 1        | 2          | 4     |
| banana       | an           | 1        | 3          | 0     |
| banana       | an           | 3        | 1          | 4     |
| banana       | an           | -1       | 1          | 4     |
| banana       | an           | -3       | 1          | 4     |
| banana       | ann          | 1        | 1          | 0     |
| helloooo     | oo           | 1        | 1          | 5     |
| helloooo     | oo           | 1        | 2          | 6     |
+--------------+--------------+----------+------------+-------+

LEFT

LEFT(value, length)

说明

返回 STRINGBYTES 值,其中包含 value 中最左侧的指定数量字符或字节。lengthINT64 类型,指定了返回值的长度。如果 valueBYTES 类型,则 length 是要返回的最左侧的字节数。如果 valueSTRING,则 length 是要返回的最左侧的字符数。

如果 length 为 0,则返回空的 STRINGBYTES 值。如果 length 为负数,则返回错误。如果 length 超出了 value 中的字符数或字节数,则返回原始的 value

返回类型

STRINGBYTES

示例

WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;

+---------+--------------+
| example | left_example |
+---------+--------------+
| apple   | app          |
| banana  | ban          |
| абвгд   | абв          |
+---------+--------------+
WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;

-- Note that the result of LEFT is of type BYTES, displayed as a base64-encoded string.
+----------+--------------+
| example  | left_example |
+----------+--------------+
| YXBwbGU= | YXBw         |
| YmFuYW5h | YmFu         |
| q83vqrs= | q83v         |
+----------+--------------+

LENGTH

LENGTH(value)

说明

返回 STRINGBYTES 值的长度。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 组成的 STRINGBYTES 值。return_lengthINT64 类型,指定了返回值的长度。如果 original_valueBYTES 类型,return_length 就是字节数。如果 original_valueSTRING 类型,return_length 就是字符数。

pattern 的默认值是空格。

original_valuepattern 必须采用相同的数据类型。

如果 return_length 小于或等于 original_value 长度,则此函数会返回 original_value 值,并截断到 return_length 值。例如,LPAD("hello world", 7); 会返回 "hello w"

如果 original_valuereturn_lengthpatternNULL,则此函数会返回 NULL

在以下情况下,此函数会返回错误:

  • return_length 为负数
  • pattern 为空

返回类型

STRINGBYTES

示例

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 的字节保留原样。

返回类型

STRINGBYTES

示例


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 相同,但仅移除前导字符。

返回类型

STRINGBYTES

示例

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

说明

获取 STRINGvalue,并执行与 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 |
+---+----+-------+-------+------+------+

OCTET_LENGTH

OCTET_LENGTH(value)

BYTE_LENGTH 的别名。

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regexp)

说明

如果 value 与正则表达式 regexp 部分匹配,则此函数返回 TRUE

如果 regexp 参数无效,此函数会返回错误。

您可使用 ^(文本开头)和 $(文本结尾)搜索完整匹配。鉴于正则表达式运算符的优先顺序,最好为 ^$ 之间的内容加上英文括号。

返回类型

BOOL

示例

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["foo@example.com", "bar@example.org", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo@example.com | true     |
| bar@example.org | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
    AS valid_email_address,
  REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
    AS without_parentheses
FROM
  (SELECT
    ["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com      | true                | true                |
| a@foo.computer | false               | true                |
| b@bar.org      | true                | true                |
| !b@bar.org     | false               | true                |
| c@buz.net      | false               | false               |
+----------------+---------------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regexp[, position[, occurrence]])

说明

返回 value 中与正则表达式 regexp 匹配的子字符串。如果不存在匹配项,则返回 NULL

如果正则表达式包含捕获组,则此函数返回与该捕获组匹配的子字符串。如果表达式不包含捕获组,则函数返回整个匹配的子字符串。

如果指定了 position,则搜索会从 value 中的此位置开始,否则会从 value 的开头开始。position 必须为正整数,不能为 0。如果 position 大于 value 的长度,则返回 NULL

如果指定了 occurrence,则搜索会返回 valueregexp 的特定出现项,否则会返回第一个匹配项。如果 occurrence 大于找到的匹配数,则返回 NULL。如果 occurrence > 1,则该函数会从上一个出现项后面的字符开始搜索额外的出现项。

出现以下情况时返回一个错误:

  • 正则表达式无效
  • 正则表达式具有多个捕获组
  • position 不是正整数
  • occurrence 不是正整数

返回类型

STRINGBYTES

示例

WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+
WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
  AS top_level_domain
FROM email_addresses;

+------------------+
| top_level_domain |
+------------------+
| com              |
| org              |
| net              |
+------------------+
WITH example AS
(SELECT 'Hello Helloo and Hellooo' AS value, 'H?ello+' AS regex, 1 as position,
1 AS occurrence UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 4 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 2, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 20, 1 UNION ALL
SELECT 'cats&dogs&rabbits' ,'\\w+&', 1, 2 UNION ALL
SELECT 'cats&dogs&rabbits', '\\w+&', 2, 3
)
SELECT value, regex, position, occurrence, REGEXP_EXTRACT(value, regex,
position, occurrence) AS regexp_value FROM example;

+--------------------------+---------+----------+------------+--------------+
| value                    | regex   | position | occurrence | regexp_value |
+--------------------------+---------+----------+------------+--------------+
| Hello Helloo and Hellooo | H?ello+ | 1        | 1          | Hello        |
| Hello Helloo and Hellooo | H?ello+ | 1        | 2          | Helloo       |
| Hello Helloo and Hellooo | H?ello+ | 1        | 3          | Hellooo      |
| Hello Helloo and Hellooo | H?ello+ | 1        | 4          | NULL         |
| Hello Helloo and Hellooo | H?ello+ | 2        | 1          | ello         |
| Hello Helloo and Hellooo | H?ello+ | 3        | 1          | Helloo       |
| Hello Helloo and Hellooo | H?ello+ | 3        | 2          | Hellooo      |
| Hello Helloo and Hellooo | H?ello+ | 3        | 3          | NULL         |
| Hello Helloo and Hellooo | H?ello+ | 20       | 1          | NULL         |
| cats&dogs&rabbits        | \w+&    | 1        | 2          | dogs&        |
| cats&dogs&rabbits        | \w+&    | 2        | 3          | NULL         |
+--------------------------+---------+----------+------------+--------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regexp)

说明

返回一个数组,该数组由 value 中与正则表达式 regexp 匹配的所有子字符串组成。

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

返回类型

STRINGBYTES 组成的 ARRAY

示例

WITH code_markdown AS
  (SELECT "Try `function(x)` or `function(y)`" as code)

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

REGEXP_INSTR

REGEXP_INSTR(source_value, regexp [, position[, occurrence, [occurrence_position]]])

说明

返回 source_value 中正则表达式 regexp 的最小索引值(从 1 开始)。如果未找到匹配项或正则表达式为空,则返回 0。如果正则表达式无效或具有多个捕获组,则返回错误。source_valueregexp 必须是同一类型(STRINGBYTES)。

如果指定了 position,则搜索会从 source_value 中的此位置开始,否则会从 source_value 的开头开始。如果 position 为负数,则该函数会从 source_value 末尾反向搜索,其中 -1 表示最后一个字符。position 不能为 0。

如果指定了 occurrence,则搜索会返回 source_valueregexp 的特定实例的位置,否则会返回第一次出现的索引。如果 occurrence 大于找到的匹配数,则返回 0。如果 occurrence > 1,则该函数会搜索重叠的出现项,换句话说,该函数会从上一个出现项中第二个字符开始搜索额外的出现项。occurrence 不能为 0 或负数。

您可以选择使用 occurrence_position 来指定相对于 occurrence 的位置。您的选择是:+ 0:返回出现项的起始位置。+ 1:返回出现项末尾后面的第一个位置。如果出现项的末尾也是输入的末尾,则返回出现项末尾对应的数字加上 1 之后的结果。例如,字符串长度 + 1。

返回类型

INT64

示例

WITH example AS (
  SELECT 'ab@gmail.com' AS source_value, '@[^.]*' AS regexp UNION ALL
  SELECT 'ab@mail.com', '@[^.]*' UNION ALL
  SELECT 'abc@gmail.com', '@[^.]*' UNION ALL
  SELECT 'abc.com', '@[^.]*')
SELECT source_value, regexp, REGEXP_INSTR(source_value, regexp) AS instr
FROM example;

+---------------+--------+-------+
| source_value  | regexp | instr |
+---------------+--------+-------+
| ab@gmail.com  | @[^.]* | 3     |
| ab@mail.com   | @[^.]* | 3     |
| abc@gmail.com | @[^.]* | 4     |
| abc.com       | @[^.]* | 0     |
+---------------+--------+-------+
WITH example AS (
  SELECT 'a@gmail.com b@gmail.com' AS source_value, '@[^.]*' AS regexp, 1 AS position UNION ALL
  SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 2 UNION ALL
  SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 3 UNION ALL
  SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 4)
SELECT
  source_value, regexp, position,
  REGEXP_INSTR(source_value, regexp, position) AS instr
FROM example;

+-------------------------+--------+----------+-------+
| source_value            | regexp | position | instr |
+-------------------------+--------+----------+-------+
| a@gmail.com b@gmail.com | @[^.]* | 1        | 2     |
| a@gmail.com b@gmail.com | @[^.]* | 2        | 2     |
| a@gmail.com b@gmail.com | @[^.]* | 3        | 14    |
| a@gmail.com b@gmail.com | @[^.]* | 4        | 14    |
+-------------------------+--------+----------+-------+
WITH example AS (
  SELECT 'a@gmail.com b@gmail.com c@gmail.com' AS source_value,
         '@[^.]*' AS regexp, 1 AS position, 1 AS occurrence UNION ALL
  SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 2 UNION ALL
  SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 3)
SELECT
  source_value, regexp, position, occurrence,
  REGEXP_INSTR(source_value, regexp, position, occurrence) AS instr
FROM example;

+-------------------------------------+--------+----------+------------+-------+
| source_value                        | regexp | position | occurrence | instr |
+-------------------------------------+--------+----------+------------+-------+
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1        | 1          | 2     |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1        | 2          | 14    |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1        | 3          | 26    |
+-------------------------------------+--------+----------+------------+-------+
WITH example AS (
  SELECT 'a@gmail.com' AS source_value, '@[^.]*' AS regexp,
         1 AS position, 1 AS occurrence, 0 AS o_position UNION ALL
  SELECT 'a@gmail.com', '@[^.]*', 1, 1, 1)
SELECT
  source_value, regexp, position, occurrence, o_position,
  REGEXP_INSTR(source_value, regexp, position, occurrence, o_position) AS instr
FROM example;

+--------------+--------+----------+------------+------------+-------+
| source_value | regexp | position | occurrence | o_position | instr |
+--------------+--------+----------+------------+------------+-------+
| a@gmail.com  | @[^.]* | 1        | 1          | 0          | 2     |
| a@gmail.com  | @[^.]* | 1        | 1          | 1          | 8     |
+--------------+--------+----------+------------+------------+-------+

REGEXP_REPLACE

REGEXP_REPLACE(value, regexp, replacement)

说明

返回一个 STRING,其中将 value 中所有与正则表达式 regexp 匹配的子字符串替换为 replacement

您可以在 replacement 参数中使用通过反斜杠转义的数字(\1 至 \9),借此在 regexp 模式中插入与带有英文括号的对应组匹配的文本。“\0”可用于引用整个匹配文本。

REGEXP_REPLACE 函数只会替换非重叠的匹配项。例如,替换 banana 中的 ana 时,此函数只会替换一处,而不是两处。

如果 regexp 参数不是有效的正则表达式,则此函数会返回错误。

返回类型

STRINGBYTES

示例

WITH markdown AS
  (SELECT "# Heading" as heading
  UNION ALL
  SELECT "# Another heading" as heading)

SELECT
  REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
  AS html
FROM markdown;

+--------------------------+
| html                     |
+--------------------------+
| <h1>Heading</h1>         |
| <h1>Another heading</h1> |
+--------------------------+

REGEXP_SUBSTR

REGEXP_SUBSTR(value, regexp[, position[, occurrence]])

说明

相当于 REGEXP_EXTRACT

返回类型

STRINGBYTES

示例

WITH example AS
(SELECT 'Hello World Helloo' AS value, 'H?ello+' AS regex, 1 AS position, 1 AS
occurrence
)
SELECT value, regex, position, occurrence, REGEXP_SUBSTR(value, regex,
position, occurrence) AS regexp_value FROM example;

+--------------------+---------+----------+------------+--------------+
| value              | regex   | position | occurrence | regexp_value |
+--------------------+---------+----------+------------+--------------+
| Hello World Helloo | H?ello+ | 1        | 1          | Hello        |
+--------------------+---------+----------+------------+--------------+

REPLACE

REPLACE(original_value, from_value, to_value)

说明

original_value 中出现的 from_value 全部替换为 to_value。如果 from_value 为空,则不执行替换。

返回类型

STRINGBYTES

示例

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 组成的 STRINGBYTES 值。repetitions 参数指定了 original_value 的重复次数。如果 original_valuerepetitionsNULL,则返回 NULL

如果 repetitions 值为负数,该函数会返回错误。

返回类型

STRINGBYTES

示例

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)

说明

返回输入 STRINGBYTES 的反转值。

返回类型

STRINGBYTES

示例

WITH example AS (
  SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
  SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
  sample_string,
  REVERSE(sample_string) AS reverse_string,
  sample_bytes,
  REVERSE(sample_bytes) AS reverse_bytes
FROM example;

+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo           | oof            | bar          | rab           |
| абвгд         | дгвба          | 123          | 321           |
+---------------+----------------+--------------+---------------+
RIGHT(value, length)

说明

返回 STRINGBYTES 值,其中包含 value 中最右侧的指定数量字符或字节。lengthINT64 类型,指定了返回值的长度。如果 valueBYTES,则 length 是要返回的最右侧的字节数。如果 valueSTRING,则 length 是要返回的最右侧的字符数。

如果 length 为 0,则返回空的 STRINGBYTES 值。如果 length 为负数,则返回错误。如果 length 超出了 value 中的字符数或字节数,则返回原始的 value

返回类型

STRINGBYTES

示例

WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;

+---------+---------------+
| example | right_example |
+---------+---------------+
| apple   | ple           |
| banana  | ana           |
| абвгд   | вгд           |
+---------+---------------+
WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;

-- Note that the result of RIGHT is of type BYTES, displayed as a base64-encoded string.
+----------+---------------+
| example  | right_example |
+----------+---------------+
| YXBwbGU= | cGxl          |
| YmFuYW5h | YW5h          |
| q83vqrs= | 76q7          |
+----------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

说明

返回由附带前缀 patternoriginal_value 组成的 STRINGBYTES 值。return_length 参数是 INT64 类型,指定了返回值的长度。如果 original_valueBYTESreturn_length 就是字节数。如果 original_valueSTRINGreturn_length 就是字符数。

pattern 的默认值是空格。

original_valuepattern 必须采用相同的数据类型。

如果 return_length 小于或等于 original_value 长度,则此函数会返回 original_value 值,并截断到 return_length 值。例如,RPAD("hello world", 7); 会返回 "hello w"

如果 original_valuereturn_lengthpatternNULL,则此函数会返回 NULL

在以下情况下,此函数会返回错误:

  • return_length 为负数
  • pattern 为空

返回类型

STRINGBYTES

示例

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 相同,但仅移除尾随字符。

返回类型

STRINGBYTES

示例

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  RTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+
WITH items AS
  (SELECT "applexxx" as item
  UNION ALL
  SELECT "bananayyy" as item
  UNION ALL
  SELECT "orangezzz" as item
  UNION ALL
  SELECT "pearxyz" as item)

SELECT
  RTRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

说明

BYTES 序列转换为 STRING。任何无效的 UTF-8 字符都会替换为 Unicode 替换字符 U+FFFD

返回类型

STRING

示例

以下语句会返回 Unicode 替换字符 �。

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

SOUNDEX

SOUNDEX(value)

说明

返回表示 valueSoundex 代码的 STRING

SOUNDEX 会生成字符串的语音表示形式。它会按声音(如英语中的发音)将字词编入索引。它通常用于帮助确定两个字符串(例如姓氏 Levine 和 Lavine 或者单词 to 和 too)是否具有相似的英语发音。

SOUNDEX 的结果由 1 个字母后跟 3 个数字组成。系统会忽略非拉丁字符。如果在移除非拉丁字符后剩余的字符串为空,则返回空的 STRING

返回类型

STRING

示例

WITH example AS (
  SELECT 'Ashcraft' AS value UNION ALL
  SELECT 'Raven' AS value UNION ALL
  SELECT 'Ribbon' AS value UNION ALL
  SELECT 'apple' AS value UNION ALL
  SELECT 'Hello world!' AS value UNION ALL
  SELECT '  H3##!@llo w00orld!' AS value UNION ALL
  SELECT '#1' AS value UNION ALL
  SELECT NULL AS value
)
SELECT value, SOUNDEX(value) AS soundex
FROM example;

+----------------------+---------+
| value                | soundex |
+----------------------+---------+
| Ashcraft             | A261    |
| Raven                | R150    |
| Ribbon               | R150    |
| apple                | a140    |
| Hello world!         | H464    |
|   H3##!@llo w00orld! | H464    |
| #1                   |         |
| NULL                 | NULL    |
+----------------------+---------+

SPLIT

SPLIT(value[, delimiter])

说明

使用 delimiter 参数拆分 value

对于 STRING,默认分隔符是英文逗号 ,

对于 BYTES,您必须指定一个分隔符。

对于 STRING 值,拆分空的分隔符会生成一个 UTF-8 字符数组,对于 BYTES 值则会生成一个 BYTES 数组。

拆分空 STRING 会返回包含一个空 STRINGARRAY

返回类型

类型为 STRINGARRAY,或类型为 BYTESARRAY

示例

WITH letters AS
  (SELECT "" as letter_group
  UNION ALL
  SELECT "a" as letter_group
  UNION ALL
  SELECT "b c d" as letter_group)

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

+----------------------+
| example              |
+----------------------+
| []                   |
| [a]                  |
| [b, c, d]            |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

说明

获取两个 STRINGBYTES 值。如果第二个值是第一个值的前缀,则返回 TRUE

返回类型

BOOL

示例

WITH items AS
  (SELECT "foo" as item
  UNION ALL
  SELECT "bar" as item
  UNION ALL
  SELECT "baz" as item)

SELECT
  STARTS_WITH(item, "b") as example
FROM items;

+---------+
| example |
+---------+
|   False |
|    True |
|    True |
+---------+

STRPOS

STRPOS(value1, value2)

说明

获取两个 STRINGBYTES 值。返回 value1 中第一次出现 value2 处的索引(从 1 开始)。如果未找到 value2,则此函数返回 0

返回类型

INT64

示例

WITH email_addresses AS
  (SELECT
    "foo@example.com" AS email_address
  UNION ALL
  SELECT
    "foobar@example.com" AS email_address
  UNION ALL
  SELECT
    "foobarbaz@example.com" AS email_address
  UNION ALL
  SELECT
    "quxexample.com" AS email_address)

SELECT
  STRPOS(email_address, "@") AS example
FROM email_addresses;

+---------+
| example |
+---------+
|       4 |
|       7 |
|      10 |
|       0 |
+---------+

SUBSTR

SUBSTR(value, position[, length])

说明

返回所提供 STRINGBYTES 值的子字符串。position 参数是一个整数,指定了子字符串的开始位置;position = 1 时,表示第一个字符或字节。length 参数是 STRING 参数的字符数上限,或 BYTES 参数的字节数上限。

如果 position 为负数,则此函数会从 value 末尾开始计数,其中 -1 表示最后一个字符。

如果 position 表示不在 STRING 左端的位置(position = 0 或 position < -LENGTH(value)),则此函数会从 position = 1 处开始计数。如果 length 超出了 value 的长度,则函数返回的字符数会少于 length

如果 length 小于 0,则此函数会返回错误。

返回类型

STRINGBYTES

示例

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

SUBSTRING

SUBSTRING(value, position[, length])

SUBSTR 的别名。

TO_BASE32

TO_BASE32(bytes_expr)

说明

BYTES 序列转换为 base32 编码的 STRING。如需将 base32 编码的 STRING 转换为 BYTES,请使用 FROM_BASE32

返回类型

STRING

示例

SELECT TO_BASE32(b'abcde\xFF') AS base32_string;

+------------------+
| base32_string    |
+------------------+
| MFRGGZDF74====== |
+------------------+

TO_BASE64

TO_BASE64(bytes_expr)

说明

BYTES 序列转换为 base64 编码的 STRING。如需将 base64 编码的 STRING 转换为 BYTES,请使用 FROM_BASE64

有几种常用的 base64 编码,它们的区别在于使用 65 个 ASCII 字符的字母来编码 64 位数字和内边距。如需了解详情,请参阅 RFC 4648。此函数会添加内边距并使用字母 [A-Za-z0-9+/=]

返回类型

STRING

示例

SELECT TO_BASE64(b'\377\340') AS base64_string;

+---------------+
| base64_string |
+---------------+
| /+A=          |
+---------------+

如需使用其他 base64 字符进行编码,您可能需要使用 REPLACE 函数编写 TO_BASE64。例如,Web 编程中常用的 base64url 网址安全和文件名安全编码方法使用 -_= 作为最后的字符,而不是 +/=。如需对 base64url 编码的字符串进行编码,请将 -_ 分别替换为 +/

SELECT REPLACE(REPLACE(TO_BASE64(b"\377\340"), "+", "-"), "/", "_") as websafe_base64;

+----------------+
| websafe_base64 |
+----------------+
| _-A=           |
+----------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

说明

接受一个并返回 INT64 类型数组。

  • 如果 valueSTRING,则所返回数组中的每个元素都代表一个码位。每个码位都位于 [0, 0xD7FF] 和 [0xE000, 0x10FFFF] 范围内。
  • 如果 valueBYTES,则数组中的每个元素都是 [0, 255] 范围内的一个扩展 ASCII 字符值。

如需将码位数组转换为 STRINGBYTES,请参阅 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_POINTSBYTES 版本会返回包含两个元素的数组,而 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       |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar                           | 666f6f626172     |
+----------------------------------+------------------+

TRANSLATE

TRANSLATE(expression, source_characters, target_characters)

说明

expression 中,将 source_characters 的每个字符替换为 target_characters 的相应字符。所有输入都必须是同一类型(STRINGBYTES)。

  • expression 中的每个字符最多转换一次。
  • 如果 expression 中的字符不在 source_characters 中,则该字符在 expression 中保持不变。
  • 如果 source_characters 中的字符在 target_characters 中没有对应的字符,则该字符会从结果中省略。
  • source_characters 中的重复字符会导致错误。

返回类型

STRINGBYTES

示例

WITH example AS (
  SELECT 'This is a cookie' AS expression, 'sco' AS source_characters, 'zku' AS
  target_characters UNION ALL
  SELECT 'A coaster' AS expression, 'co' AS source_characters, 'k' as
  target_characters
)
SELECT expression, source_characters, target_characters, TRANSLATE(expression,
source_characters, target_characters) AS translate
FROM example;

+------------------+-------------------+-------------------+------------------+
| expression       | source_characters | target_characters | translate        |
+------------------+-------------------+-------------------+------------------+
| This is a cookie | sco               | zku               | Thiz iz a kuukie |
| A coaster        | co                | k                 | A kaster         |
+------------------+-------------------+-------------------+------------------+

TRIM

TRIM(value1[, value2])

说明

移除与 value2 匹配的所有前导和尾随字符。如果未指定 value2,则移除所有前导和尾随空格(如 Unicode 标准定义)。如果第一个参数是 BYTES 类型,则必须提供第二个参数。

如果 value2 包含多个字符或字节,则该函数会移除 value2 中包含的所有前导或尾随字符或字节。

返回类型

STRINGBYTES

示例

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

UNICODE

UNICODE(value)

说明

返回 value 中第一个字符的 Unicode 代码点。如果 value 为空或者生成的 Unicode 代码点为 0,则返回 0

返回类型

INT64

示例

SELECT UNICODE('âbcd') as A, UNICODE('â') as B, UNICODE('') as C, UNICODE(NULL) as D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| 226   | 226   | 0     | NULL  |
+-------+-------+-------+-------+

UPPER

UPPER(value)

说明

对于 STRING 参数,该函数会返回所有字母字符均为大写的原始字符串。它根据 Unicode 字符数据库进行大写与小写之间的映射,而不考虑特定语言的映射。

对于 BYTES 参数,此函数会将其视为 ASCII 文本,并将所有大于 127 的字节保留原样。

返回类型

STRINGBYTES

示例

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