These string functions work on STRING data. STRING values must be well-formed UTF-8.
All string comparisons are done byte-by-byte, without regard to Unicode canonical equivalence.
CHAR_LENGTH
CHAR_LENGTH(value)
Description
Returns the length of the STRING in characters.
Return type
INT64
Examples
Table example:
+----------------+
| characters |
+----------------+
| абвгд |
+----------------+
SELECT
characters,
CHAR_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CHARACTER_LENGTH
CHARACTER_LENGTH(value)
Description
Synonym for CHAR_LENGTH.
Return type
INT64
Examples
Table example:
+----------------+
| characters |
+----------------+
| абвгд |
+----------------+
SELECT
characters,
CHARACTER_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CONCAT
CONCAT(value1[, ...])
Description
Concatenates up to five values into a single result.
Return type
STRING
Examples
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)
Description
Takes two values. Returns TRUE if the second value is a suffix of the first.
Return type
BOOL
Examples
Table items:
+----------------+
| item |
+----------------+
| apple |
| banana |
| orange |
+----------------+
SELECT
ENDS_WITH(item, "e") as example
FROM items;
+---------+
| example |
+---------+
| True |
| False |
| True |
+---------+
LTRIM
LTRIM(value1[, value2])
Description
Identical to TRIM, but only removes leading characters.
Return type
STRING
Examples
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)
Description
Replaces all occurrences of from_value with to_value in original_value.
If from_value is empty, no replacement is made.
Return type
STRING
Examples
+--------------------+
| 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)
Description
Returns the reverse of the input STRING.
Return type
STRING
Examples
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])
Description
Identical to TRIM, but only removes trailing characters.
Return type
STRING
Examples
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)
Description
Takes two values. Returns TRUE if the second value is a prefix of the first.
Return type
BOOL
Examples
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])
Description
Returns a substring of the supplied value. The
position argument is an integer specifying the starting position of the
substring, with position = 1 indicating the first character or byte. The
length argument is the maximum number of characters for STRING arguments.
If position is negative, the function counts from the end of value,
with -1 indicating the last character.
If position is a position off the left end of the
STRING (position = 0 or
position < -LENGTH(value)), the function starts
from position = 1. If length exceeds the length of
value, returns fewer than length characters.
If length is less than 0, the function returns an error.
Return type
STRING
Examples
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])
Description
Removes all leading and trailing characters that match value2. If value2 is
not specified, all leading and trailing whitespace characters (as defined by the
Unicode standard) are removed.
If value2 contains more than one character, the function removes all leading
or trailing characters contained in value2.
Return type
STRING
Examples
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 |
+---------+