Beam ZetaSQL 字串函式

此頁面說明 Beam ZetaSQL 支援的 ZetaSQL 字串函式。

這些字串函式適用於 STRING 資料。STRING 值必須是格式正確的 UTF-8。所有字串比較都是逐位元組完成，不考慮 Unicode 正規等價。

運算子語法	說明
CHAR_LENGTH(value)	傳回字串的字元長度
CHARACTER_LENGTH(value)	CHAR_LENGTH 的同義詞
CONCAT(value1[, …])	將最多五個值串連成單一結果
ENDS_WITH(value1, value2)	如果第二個值是第一個值的後綴，則傳回 TRUE
LTRIM(value1[, value2])	與 TRIM 相同，但僅移除前導字元。
REPLACE(original_value, from_value, to_value)	將 `original_value` 中所有出現的 `from_value` 取代為 `to_value`
REVERSE(value)	傳回輸入字串的反轉
RTRIM(value1[, value2])	與 TRIM 相同，但僅移除尾隨字元
STARTS_WITH(value1, value2)	如果第二個值是第一個值的前綴，則傳回 TRUE。
SUBSTR(value, position[, length])	傳回所提供值的子字串
TRIM(value1[, value2])	移除所有符合 `value2` 的前導和尾隨字元

CHAR_LENGTH

CHAR_LENGTH(value)

說明

傳回 STRING 的字元長度。

傳回型別

INT64

範例


Table example:

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

範例


Table example:

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

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

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

CONCAT

CONCAT(value1[, ...])

說明

將最多五個值串連成單一結果。

傳回型別

STRING

範例


Table Employees:

+-------------+-----------+
| first_name  | last_name |
+-------------+-----------+
| John        | Doe       |
| Jane        | Smith     |
| Joe         | Jackson   |
+-------------+-----------+

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

範例


Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

SELECT
  ENDS_WITH(item, "e") as example
FROM items;

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

LTRIM

LTRIM(value1[, value2])

說明

與 TRIM 相同，但僅移除前導字元。

傳回型別

STRING

範例


Table items:

+----------------+
| item           |
+----------------+
|    apple       |
|    banana      |
|    orange      |
+----------------+

SELECT
  CONCAT("#", LTRIM(item), "#") as example
FROM items;

+-------------+
| example     |
+-------------+
| #apple   #  |
| #banana   # |
| #orange   # |
+-------------+


Table items:

+----------------+
| item           |
+----------------+
| ***apple***    |
| ***banana***   |
| ***orange***   |
+----------------+

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

+-----------+
| example   |
+-----------+
| apple***  |
| banana*** |
| orange*** |
+-----------+


Table items:

+----------------+
| item           |
+----------------+
| xxxapplexxx    |
| yyybananayyy   |
| zzzorangezzz   |
| xyzpearzyz     |
+----------------+

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

+-----------+
| example   |
+-----------+
| applexxx  |
| bananayyy |
| orangezzz |
| pearxyz   |
+-----------+

REPLACE

REPLACE(original_value, from_value, to_value)

說明

將 original_value 中所有出現的 from_value 取代為 to_value。如果 from_value 為空，則不進行取代。

傳回型別

STRING

範例


+--------------------+
| dessert            |
+--------------------+
| apple pie          |
| blackberry pie     |
| cherry pie         |
+--------------------+

SELECT
  REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;

+--------------------+
| example            |
+--------------------+
| apple cobbler      |
| blackberry cobbler |
| cherry cobbler     |
+--------------------+

REVERSE

REVERSE(value)

說明

傳回輸入 STRING 的反轉。

傳回型別

STRING

範例

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

+---------------+----------------+
| sample_string | reverse_string |
+---------------+----------------+
| foo           | oof            |
| абвгд         | дгвба          |
+---------------+----------------+

RTRIM

RTRIM(value1[, value2])

說明

與 TRIM 相同，但僅移除尾隨字元。

傳回型別

STRING

範例


Table items:

+----------------+
| item           |
+----------------+
| ***apple***    |
| ***banana***   |
| ***orange***   |
+----------------+

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

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+


Table items:

+----------------+
| item           |
+----------------+
| applexxx       |
| bananayyy      |
| orangezzz      |
| pearxyz        |
+----------------+

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

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

STARTS_WITH

STARTS_WITH(value1, value2)

說明

接受兩個值。如果第二個值是第一個值的前綴，則傳回 TRUE。

傳回型別

BOOL

範例


SELECT
  STARTS_WITH(item, "b") as example
FROM (
  SELECT "foo" as item
  UNION ALL SELECT "bar" as item
  UNION ALL SELECT "baz" as item) AS items;


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

SUBSTR

SUBSTR(value, position[, length])

說明

傳回所提供值的子字串。position 引數是一個整數，指定子字串的起始位置，其中 position = 1 表示第一個字元或位元組。length 引數是 STRING 引數的最大字元數。

如果 position 是負數，函式會從 value 的結尾開始計算，其中 -1 表示最後一個字元。

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

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

傳回型別

STRING

範例


Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

SELECT
  SUBSTR(item, 2) as example
FROM items;

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+


Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

SELECT
  SUBSTR(item, 2, 2) as example
FROM items;

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+


Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

SELECT
  SUBSTR(item, -2) as example
FROM items;

+---------+
| example |
+---------+
| le      |
| na      |
| ge      |
+---------+

TRIM

TRIM(value1[, value2])

說明

移除所有符合 value2 的前導和尾隨字元。如果未指定 value2，則會移除所有前導和尾隨空白字元（由 Unicode 標準定義）。

如果 value2 包含多個字元，則函式會移除 value2 中包含的所有前導或尾隨字元。

傳回型別

STRING

範例


Table items:

+----------------+
| item           |
+----------------+
|    apple       |
|    banana      |
|    orange      |
+----------------+

SELECT
  CONCAT("#", TRIM(item), "#") as example
FROM items;

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+


Table items:

+----------------+
| item           |
+----------------+
| ***apple***    |
| ***banana***   |
| ***orange***   |
+----------------+

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

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


Table items:

+----------------+
| item           |
+----------------+
| xxxapplexxx    |
| yyybananayyy   |
| zzzorangezzz   |
| xyzpearxyz     |
+----------------+

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

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