String functions

ASCII(value)

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(value)

SELECT BYTE_LENGTH('абвгд') AS string_example;

/*----------------*
 | string_example |
 +----------------+
 | 10             |
 *----------------*/

SELECT BYTE_LENGTH(b'абвгд') AS bytes_example;

/*----------------*
 | bytes_example  |
 +----------------+
 | 10             |
 *----------------*/

CHAR_LENGTH(value)

SELECT CHAR_LENGTH('абвгд') AS char_length;

/*-------------*
 | char_length |
 +-------------+
 | 5           |
 *------------ */

CHR(value)

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(ascii_code_points)

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== |
 *----------*/

CODE_POINTS_TO_STRING(unicode_code_points)

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   |
 *--------*/

CONCAT(value1[, ...])

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(value, suffix)

SELECT ENDS_WITH('apple', 'e') as example

/*---------*
 | example |
 +---------+
 |    True |
 *---------*/

FORMAT(format_string_expression, data_type_expression[, ...])

SELECT FORMAT('date: %s!', FORMAT_DATE('%B %d, %Y', date '2015-01-02'));

date: January 02, 2015!

%[flags][width][.precision]specifier

FORMAT('%s', 1)

FORMAT('%')

FORMAT('00-%t-00', NULL_expression);

00-NULL-00

FROM_BASE32(string_expr)

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(string_expr)

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=      |
 *-----------*/

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(string)

INITCAP(value[, delimiters])

SELECT
  'Hello World-everyone!' AS value,
  INITCAP('Hello World-everyone!') AS initcap_value

/*-------------------------------+-------------------------------*
 | value                         | initcap_value                 |
 +-------------------------------+-------------------------------+
 | Hello World-everyone!         | Hello World-Everyone!         |
 *-------------------------------+-------------------------------*/

SELECT
  'Apples1oranges2pears' as value,
  '12' AS delimiters,
  INITCAP('Apples1oranges2pears' , '12') AS initcap_value

/*----------------------+------------+----------------------*
 | value                | delimiters | initcap_value        |
 +----------------------+------------+----------------------+
 | Apples1oranges2pears | 12         | Apples1Oranges2Pears |
 *----------------------+------------+----------------------*/

INSTR(value, subvalue[, position[, occurrence]])

SELECT
  'banana' AS value, 'an' AS subvalue, 1 AS position, 1 AS occurrence,
  INSTR('banana', 'an', 1, 1) AS instr;

/*--------------+--------------+----------+------------+-------*
 | value        | subvalue     | position | occurrence | instr |
 +--------------+--------------+----------+------------+-------+
 | banana       | an           | 1        | 1          | 2     |
 *--------------+--------------+----------+------------+-------*/

SELECT
  'banana' AS value, 'an' AS subvalue, 1 AS position, 2 AS occurrence,
  INSTR('banana', 'an', 1, 2) AS instr;

/*--------------+--------------+----------+------------+-------*
 | value        | subvalue     | position | occurrence | instr |
 +--------------+--------------+----------+------------+-------+
 | banana       | an           | 1        | 2          | 4     |
 *--------------+--------------+----------+------------+-------*/

SELECT
  'banana' AS value, 'an' AS subvalue, 1 AS position, 3 AS occurrence,
  INSTR('banana', 'an', 1, 3) AS instr;

/*--------------+--------------+----------+------------+-------*
 | value        | subvalue     | position | occurrence | instr |
 +--------------+--------------+----------+------------+-------+
 | banana       | an           | 1        | 3          | 0     |
 *--------------+--------------+----------+------------+-------*/

SELECT
  'banana' AS value, 'an' AS subvalue, 3 AS position, 1 AS occurrence,
  INSTR('banana', 'an', 3, 1) AS instr;

/*--------------+--------------+----------+------------+-------*
 | value        | subvalue     | position | occurrence | instr |
 +--------------+--------------+----------+------------+-------+
 | banana       | an           | 3        | 1          | 4     |
 *--------------+--------------+----------+------------+-------*/

SELECT
  'banana' AS value, 'an' AS subvalue, -1 AS position, 1 AS occurrence,
  INSTR('banana', 'an', -1, 1) AS instr;

/*--------------+--------------+----------+------------+-------*
 | value        | subvalue     | position | occurrence | instr |
 +--------------+--------------+----------+------------+-------+
 | banana       | an           | -1       | 1          | 4     |
 *--------------+--------------+----------+------------+-------*/

SELECT
  'banana' AS value, 'an' AS subvalue, -3 AS position, 1 AS occurrence,
  INSTR('banana', 'an', -3, 1) AS instr;

/*--------------+--------------+----------+------------+-------*
 | value        | subvalue     | position | occurrence | instr |
 +--------------+--------------+----------+------------+-------+
 | banana       | an           | -3       | 1          | 4     |
 *--------------+--------------+----------+------------+-------*/

SELECT
  'banana' AS value, 'ann' AS subvalue, 1 AS position, 1 AS occurrence,
  INSTR('banana', 'ann', 1, 1) AS instr;

/*--------------+--------------+----------+------------+-------*
 | value        | subvalue     | position | occurrence | instr |
 +--------------+--------------+----------+------------+-------+
 | banana       | ann          | 1        | 1          | 0     |
 *--------------+--------------+----------+------------+-------*/

SELECT
  'helloooo' AS value, 'oo' AS subvalue, 1 AS position, 1 AS occurrence,
  INSTR('helloooo', 'oo', 1, 1) AS instr;

/*--------------+--------------+----------+------------+-------*
 | value        | subvalue     | position | occurrence | instr |
 +--------------+--------------+----------+------------+-------+
 | helloooo     | oo           | 1        | 1          | 5     |
 *--------------+--------------+----------+------------+-------*/

SELECT
  'helloooo' AS value, 'oo' AS subvalue, 1 AS position, 2 AS occurrence,
  INSTR('helloooo', 'oo', 1, 2) AS instr;

/*--------------+--------------+----------+------------+-------*
 | value        | subvalue     | position | occurrence | instr |
 +--------------+--------------+----------+------------+-------+
 | helloooo     | oo           | 1        | 2          | 6     |
 *--------------+--------------+----------+------------+-------*/

LEFT(value, length)

SELECT LEFT('banana', 3) AS results

/*---------*
 | results |
  +--------+
 | ban     |
 *---------*/

SELECT LEFT(b'\xab\xcd\xef\xaa\xbb', 3) AS results

-- Note that the result of LEFT is of type BYTES, displayed as a base64-encoded string.
/*---------*
 | results |
 +---------+
 | q83v    |
 *---------*/

LENGTH(value)

SELECT
  LENGTH('абвгд') AS string_example,
  LENGTH(CAST('абвгд' AS BYTES)) AS bytes_example;

/*----------------+---------------*
 | string_example | bytes_example |
 +----------------+---------------+
 | 5              | 10            |
 *----------------+---------------*/

LOWER(value)

SELECT
  LOWER('FOO BAR BAZ') AS example
FROM items;

/*-------------*
 | example     |
 +-------------+
 | foo bar baz |
 *-------------*/

LPAD(original_value, return_length[, pattern])

SELECT FORMAT('%T', LPAD('c', 5)) AS results

/*---------*
 | results |
 +---------+
 | "    c" |
 *---------*/

SELECT LPAD('b', 5, 'a') AS results

/*---------*
 | results |
 +---------+
 | aaaab   |
 *---------*/

SELECT LPAD('abc', 10, 'ghd') AS results

/*------------*
 | results    |
 +------------+
 | ghdghdgabc |
 *------------*/

SELECT LPAD('abc', 2, 'd') AS results

/*---------*
 | results |
 +---------+
 | ab      |
 *---------*/

SELECT FORMAT('%T', LPAD(b'abc', 10, b'ghd')) AS results

/*---------------*
 | results       |
 +---------------+
 | b"ghdghdgabc" |
 *---------------*/

LTRIM(value1[, value2])

SELECT CONCAT('#', LTRIM('   apple   '), '#') AS example

/*-------------*
 | example     |
 +-------------+
 | #apple #    |
 *-------------*/

SELECT LTRIM('***apple***', '*') AS example

/*-----------*
 | example   |
 +-----------+
 | apple***  |
 *-----------*/

SELECT LTRIM('xxxapplexxx', 'xyz') AS example

/*-----------*
 | example   |
 +-----------+
 | applexxx  |
 *-----------*/

NORMALIZE(value[, normalization_mode])

SELECT
  NORMALIZE('\u00ea') as a,
  NORMALIZE('\u0065\u0302') as b,
  NORMALIZE('\u00ea') = NORMALIZE('\u0065\u0302') as normalized;

/*---+---+------------*
 | a | b | normalized |
 +---+---+------------+
 | ê | ê | TRUE       |
 *---+---+------------*/

SELECT NORMALIZE('Raha\u2004Mahan', NFKC) AS normalized_name

/*-----------------*
 | normalized_name |
 +-----------------+
 | Raha Mahan      |
 *-----------------*/

SELECT NORMALIZE('Raha\u2005Mahan', NFKC) AS normalized_name

/*-----------------*
 | normalized_name |
 +-----------------+
 | Raha Mahan      |
 *-----------------*/

SELECT NORMALIZE('Raha\u2006Mahan', NFKC) AS normalized_name

/*-----------------*
 | normalized_name |
 +-----------------+
 | Raha Mahan      |
 *-----------------*/

SELECT NORMALIZE('Raha Mahan', NFKC) AS normalized_name

/*-----------------*
 | normalized_name |
 +-----------------+
 | Raha Mahan      |
 *-----------------*/

NORMALIZE_AND_CASEFOLD(value[, normalization_mode])

SELECT
  NORMALIZE('The red barn') = NORMALIZE('The Red Barn') AS normalized,
  NORMALIZE_AND_CASEFOLD('The red barn')
    = NORMALIZE_AND_CASEFOLD('The Red Barn') AS normalized_with_case_folding;

/*------------+------------------------------*
 | normalized | normalized_with_case_folding |
 +------------+------------------------------+
 | FALSE      | TRUE                         |
 *------------+------------------------------*/

SELECT
  '\u2168' AS a,
  'IX' AS b,
  NORMALIZE_AND_CASEFOLD('\u2168', NFD)=NORMALIZE_AND_CASEFOLD('IX', NFD) AS nfd,
  NORMALIZE_AND_CASEFOLD('\u2168', NFC)=NORMALIZE_AND_CASEFOLD('IX', NFC) AS nfc,
  NORMALIZE_AND_CASEFOLD('\u2168', NFKD)=NORMALIZE_AND_CASEFOLD('IX', NFKD) AS nkfd,
  NORMALIZE_AND_CASEFOLD('\u2168', NFKC)=NORMALIZE_AND_CASEFOLD('IX', NFKC) AS nkfc;

/*---+----+-------+-------+------+------*
 | a | b  | nfd   | nfc   | nkfd | nkfc |
 +---+----+-------+-------+------+------+
 | Ⅸ | IX | false | false | true | true |
 *---+----+-------+-------+------+------*/

SELECT
  '\u0041\u030A' AS a,
  '\u00C5' AS b,
  NORMALIZE_AND_CASEFOLD('\u0041\u030A', NFD)=NORMALIZE_AND_CASEFOLD('\u00C5', NFD) AS nfd,
  NORMALIZE_AND_CASEFOLD('\u0041\u030A', NFC)=NORMALIZE_AND_CASEFOLD('\u00C5', NFC) AS nfc,
  NORMALIZE_AND_CASEFOLD('\u0041\u030A', NFKD)=NORMALIZE_AND_CASEFOLD('\u00C5', NFKD) AS nkfd,
  NORMALIZE_AND_CASEFOLD('\u0041\u030A', NFKC)=NORMALIZE_AND_CASEFOLD('\u00C5', NFKC) AS nkfc;

/*---+----+-------+-------+------+------*
 | a | b  | nfd   | nfc   | nkfd | nkfc |
 +---+----+-------+-------+------+------+
 | Å | Å  | true  | true  | true | true |
 *---+----+-------+-------+------+------*/

OCTET_LENGTH(value)

REGEXP_CONTAINS(value, regexp)

SELECT
  'foo@example.com' AS email,
  REGEXP_CONTAINS('foo@example.com', r'@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+') AS is_valid

/*-----------------+----------*
 | email           | is_valid |
 +-----------------+----------+
 | foo@example.com | TRUE     |
 *-----------------+----------*/
 ```

 ```sql
SELECT
  'www.example.net' AS email,
  REGEXP_CONTAINS('www.example.net', r'@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+') AS is_valid

/*-----------------+----------*
 | email           | is_valid |
 +-----------------+----------+
 | www.example.net | FALSE    |
 *-----------------+----------*/
 ```

The following queries check to see if an email is valid. They
perform a full match, using `^` and `$`. Due to regular expression operator
precedence, it is good practice to use parentheses around everything between `^`
and `$`.

```sql
SELECT
  'a@foo.com' AS email,
  REGEXP_CONTAINS('a@foo.com', r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$') AS valid_email_address,
  REGEXP_CONTAINS('a@foo.com', r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$') AS without_parentheses;

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | a@foo.com      | true                | true                |
 *----------------+---------------------+---------------------*/

SELECT
  'a@foo.computer' AS email,
  REGEXP_CONTAINS('a@foo.computer', r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$') AS valid_email_address,
  REGEXP_CONTAINS('a@foo.computer', r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$') AS without_parentheses;

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | a@foo.computer | false               | true                |
 *----------------+---------------------+---------------------*/

SELECT
  'b@bar.org' AS email,
  REGEXP_CONTAINS('b@bar.org', r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$') AS valid_email_address,
  REGEXP_CONTAINS('b@bar.org', r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$') AS without_parentheses;

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | b@bar.org      | true                | true                |
 *----------------+---------------------+---------------------*/

SELECT
  '!b@bar.org' AS email,
  REGEXP_CONTAINS('!b@bar.org', r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$') AS valid_email_address,
  REGEXP_CONTAINS('!b@bar.org', r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$') AS without_parentheses;

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | !b@bar.org     | false               | true                |
 *----------------+---------------------+---------------------*/

SELECT
  'c@buz.net' AS email,
  REGEXP_CONTAINS('c@buz.net', r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$') AS valid_email_address,
  REGEXP_CONTAINS('c@buz.net', r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$') AS without_parentheses;

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | c@buz.net      | false               | false               |
 *----------------+---------------------+---------------------*/

REGEXP_EXTRACT(value, regexp)

SELECT REGEXP_EXTRACT('foo@example.com', r'^[a-zA-Z0-9_.+-]+') AS user_name

/*-----------*
 | user_name |
 +-----------+
 | foo       |
 *-----------*/

SELECT REGEXP_EXTRACT('foo@example.com', r'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)')

/*------------------*
 | top_level_domain |
 +------------------+
 | com              |
 *------------------*/

SELECT
  REGEXP_EXTRACT('ab', '.b') AS result_a,
  REGEXP_EXTRACT('ab', '(.)b') AS result_b,
  REGEXP_EXTRACT('xyztb', '(.)+b') AS result_c,
  REGEXP_EXTRACT('ab', '(z)?b') AS result_d

/*-------------------------------------------*
 | result_a | result_b | result_c | result_d |
 +-------------------------------------------+
 | ab       | a        | t        | NULL     |
 *-------------------------------------------*/

REGEXP_EXTRACT_ALL(value, regexp)

SELECT REGEXP_EXTRACT_ALL('Try `func(x)` or `func(y)`', '`(.+?)`') AS example

/*--------------------*
 | example            |
 +--------------------+
 | [func(x), func(y)] |
 *--------------------*/

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

SELECT
  REGEXP_INSTR('ab@cd-ef',  '@[^-]*') AS instr_a,
  REGEXP_INSTR('ab@d-ef',   '@[^-]*') AS instr_b,
  REGEXP_INSTR('abc@cd-ef', '@[^-]*') AS instr_c,
  REGEXP_INSTR('abc-ef',    '@[^-]*') AS instr_d,

/*---------------------------------------*
 | instr_a | instr_b | instr_c | instr_d |
 +---------------------------------------+
 | 3       | 3       | 4       | 0       |
 *---------------------------------------*/

SELECT
  REGEXP_INSTR('a@cd-ef b@cd-ef', '@[^-]*', 1) AS instr_a,
  REGEXP_INSTR('a@cd-ef b@cd-ef', '@[^-]*', 2) AS instr_b,
  REGEXP_INSTR('a@cd-ef b@cd-ef', '@[^-]*', 3) AS instr_c,
  REGEXP_INSTR('a@cd-ef b@cd-ef', '@[^-]*', 4) AS instr_d,

/*---------------------------------------*
 | instr_a | instr_b | instr_c | instr_d |
 +---------------------------------------+
 | 2       | 2       | 10      | 10      |
 *---------------------------------------*/

SELECT
  REGEXP_INSTR('a@cd-ef b@cd-ef c@cd-ef', '@[^-]*', 1, 1) AS instr_a,
  REGEXP_INSTR('a@cd-ef b@cd-ef c@cd-ef', '@[^-]*', 1, 2) AS instr_b,
  REGEXP_INSTR('a@cd-ef b@cd-ef c@cd-ef', '@[^-]*', 1, 3) AS instr_c

/*-----------------------------*
 | instr_a | instr_b | instr_c |
 +-----------------------------+
 | 2       | 10      | 18      |
 *-----------------------------*/

SELECT
  REGEXP_INSTR('a@cd-ef', '@[^-]*', 1, 1, 0) AS instr_a,
  REGEXP_INSTR('a@cd-ef', '@[^-]*', 1, 1, 1) AS instr_b

/*-------------------*
 | instr_a | instr_b |
 +-------------------+
 | 2       | 5       |
 *-------------------*/

REGEXP_REPLACE(value, regexp, replacement)

SELECT REGEXP_REPLACE('# Heading', r'^# ([a-zA-Z0-9\s]+$)', '<h1>\\1</h1>') AS html

/*--------------------------*
 | html                     |
 +--------------------------+
 | <h1>Heading</h1>         |
 *--------------------------*/

REPEAT(original_value, repetitions)

SELECT REPEAT('abc', 3) AS results

/*-----------*
 | results   |
 |-----------|
 | abcabcabc |
 *-----------*/

SELECT REPEAT('abc', NULL) AS results

/*---------*
 | results |
 |---------|
 | NULL    |
 *---------*/

SELECT REPEAT(NULL, 3) AS results

/*---------*
 | results |
 |---------|
 | NULL    |
 *---------*/

REPLACE(original_value, from_pattern, to_pattern)

REVERSE(value)

SELECT REVERSE('abc') AS results

/*---------*
 | results |
 +---------+
 | cba     |
 *---------*/

SELECT FORMAT('%T', REVERSE(b'1a3')) AS results

/*---------*
 | results |
 +---------+
 | b"3a1"  |
 *---------*/

RIGHT(value, length)

SELECT 'apple' AS example, RIGHT('apple', 3) AS right_example

/*---------+---------------*
 | example | right_example |
 +---------+---------------+
 | apple   | ple           |
 *---------+---------------*/

SELECT b'apple' AS example, RIGHT(b'apple', 3) AS right_example

-- Note that the result of RIGHT is of type BYTES, displayed as a base64-encoded string.
/*----------+---------------*
 | example  | right_example |
 +----------+---------------+
 | YXBwbGU= | cGxl          |
 *----------+---------------*/

RPAD(original_value, return_length[, pattern])

SELECT FORMAT('%T', RPAD('c', 5)) AS results

/*---------*
 | results |
 +---------+
 | "c    " |
 *---------*/

SELECT RPAD('b', 5, 'a') AS results

/*---------*
 | results |
 +---------+
 | baaaa   |
 *---------*/

SELECT RPAD('abc', 10, 'ghd') AS results

/*------------*
 | results    |
 +------------+
 | abcghdghdg |
 *------------*/

SELECT RPAD('abc', 2, 'd') AS results

/*---------*
 | results |
 +---------+
 | ab      |
 *---------*/

SELECT FORMAT('%T', RPAD(b'abc', 10, b'ghd')) AS results

/*---------------*
 | results       |
 +---------------+
 | b"abcghdghdg" |
 *---------------*/

RTRIM(value1[, value2])

SELECT RTRIM('***apple***', '*') AS example

/*-----------*
 | example   |
 +-----------+
 | ***apple  |
 *-----------*/

SELECT RTRIM('applexxz', 'xyz') AS example

/*---------*
 | example |
 +---------+
 | apple   |
 *---------*/

SAFE_CONVERT_BYTES_TO_STRING(value)

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

SOUNDEX(value)

SELECT 'Ashcraft' AS value, SOUNDEX('Ashcraft') AS soundex

/*----------------------+---------*
 | value                | soundex |
 +----------------------+---------+
 | Ashcraft             | A261    |
 *----------------------+---------*/

SPLIT(value[, delimiter])

STARTS_WITH(value, prefix)

SELECT STARTS_WITH('bar', 'b') AS example

/*---------*
 | example |
 +---------+
 |    True |
 *---------*/

STRPOS(value, subvalue)

SELECT STRPOS('foo@example.com', '@') AS example

/*---------*
 | example |
 +---------+
 |       4 |
 *---------*/

SUBSTR(value, position[, length])

SELECT SUBSTR('apple', 2) AS example

/*---------*
 | example |
 +---------+
 | pple    |
 *---------*/

SELECT SUBSTR('apple', 2, 2) AS example

/*---------*
 | example |
 +---------+
 | pp      |
 *---------*/

SELECT SUBSTR('apple', -2) AS example

/*---------*
 | example |
 +---------+
 | le      |
 *---------*/

SELECT SUBSTR('apple', 1, 123) AS example

/*---------*
 | example |
 +---------+
 | apple   |
 *---------*/

SELECT SUBSTR('apple', 123) AS example

/*---------*
 | example |
 +---------+
 |         |
 *---------*/

SELECT SUBSTR('apple', 123, 5) AS example

/*---------*
 | example |
 +---------+
 |         |
 *---------*/

SUBSTRING(value, position[, length])

TO_BASE32(bytes_expr)

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

/*------------------*
 | base32_string    |
 +------------------+
 | MFRGGZDF74====== |
 *------------------*/

TO_BASE64(bytes_expr)

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

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

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

/*----------------*
 | websafe_base64 |
 +----------------+
 | _-A=           |
 *----------------*/

TO_CODE_POINTS(value)

SELECT
  'foo' AS word,
  TO_CODE_POINTS('foo') AS code_points

/*---------+------------------------------------*
 | word    | code_points                        |
 +---------+------------------------------------+
 | foo     | [102, 111, 111]                    |
 *---------+------------------------------------*/

SELECT
  'bar' AS word,
  TO_CODE_POINTS('bar') AS code_points

/*---------+------------------------------------*
 | word    | code_points                        |
 +---------+------------------------------------+
 | bar     | [98, 97, 114]                      |
 *---------+------------------------------------*/

SELECT
  'baz' AS word,
  TO_CODE_POINTS('baz') AS code_points

/*---------+------------------------------------*
 | word    | code_points                        |
 +---------+------------------------------------+
 | baz     | [98, 97, 122]                      |
 *---------+------------------------------------*/

SELECT
  'giraffe' AS word,
  TO_CODE_POINTS('giraffe') AS code_points

/*---------+------------------------------------*
 | word    | code_points                        |
 +---------+------------------------------------+
 | giraffe | [103, 105, 114, 97, 102, 102, 101] |
 *---------+------------------------------------*/

SELECT
  'llama' AS word,
  TO_CODE_POINTS('llama') AS code_points

/*---------+------------------------------------*
 | word    | code_points                        |
 +---------+------------------------------------+
 | llama   | [108, 108, 97, 109, 97]            |
 *---------+------------------------------------*/

SELECT
  b'\x66\x6f\x6f' AS bytes_value,
  TO_CODE_POINTS(b'\x66\x6f\x6f') AS bytes_value_as_integer

/*------------------+------------------------*
 | bytes_value      | bytes_value_as_integer |
 +------------------+------------------------+
 | foo              | [102, 111, 111]        |
 *------------------+------------------------*/

SELECT
  b'\x00\x01\x10\xff' AS bytes_value,
  TO_CODE_POINTS(b'\x00\x01\x10\xff') AS bytes_value_as_integer

/*------------------+------------------------*
 | bytes_value      | bytes_value_as_integer |
 +------------------+------------------------+
 | \x00\x01\x10\xff | [0, 1, 16, 255]        |
 *------------------+------------------------*/

SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;

/*------------+----------*
 | b_result   | s_result |
 +------------+----------+
 | [196, 128] | [256]    |
 *------------+----------*/

TO_HEX(bytes)

SELECT
  b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_string,
  TO_HEX(b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF') AS hex_string

/*----------------------------------+------------------*
 | byte_string                      | hex_string       |
 +----------------------------------+------------------+
 | \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
 *----------------------------------+------------------*/

TO_INT64(bytes_value)

SELECT TO_INT64(b'\x00\x00\x00\x00\x00\x00\x00d') AS integer_value

/*---------------------*
 | integer_value       |
 +---------------------+
 | 100                 |
 *---------------------*/

TO_VECTOR32(bytes_value)

SELECT TO_VECTOR32(b'\x3f\xc0\x00\x00\x42\xc8\x00\x00') AS array_val

/*---------------------*
 | array_val           |
 +---------------------+
 | [ 1.5, 100.0 ]      |
 *---------------------*/

TO_VECTOR64(bytes_value)

SELECT TO_VECTOR64(b'\x40\x5e\xdc\xcc\xcc\xcc\xcc\xcd\x40\x4c\x63\xd7\x0a\x3d\x70\xa4') AS array_val

/*---------------------*
 | array_val           |
 +---------------------+
 | [ 123.45, 56.78 ]   |
 *---------------------*/

TRANSLATE(expression, source_characters, target_characters)

SELECT TRANSLATE('This is a cookie', 'sco', 'zku') AS translate

/*------------------*
 | translate        |
 +------------------+
 | Thiz iz a kuukie |
 *------------------*/

TRIM(value_to_trim[, set_of_characters_to_remove])

SELECT CONCAT('#', TRIM( '   apple   '), '#') AS example

/*----------*
 | example  |
 +----------+
 | #apple#  |
 *----------*/

SELECT TRIM('***apple***', '*') AS example

/*---------*
 | example |
 +---------+
 | apple   |
 *---------*/

SELECT TRIM('xzxapplexxy', 'xyz') as example

/*---------*
 | example |
 +---------+
 | apple   |
 *---------*/

SELECT
  TRIM('abaW̊', 'Y̊') AS a,
  TRIM('W̊aba', 'Y̊') AS b,
  TRIM('abaŪ̊', 'Y̊') AS c,
  TRIM('Ū̊aba', 'Y̊') AS d

/*------+------+------+------*
 | a    | b    | c    | d    |
 +------+------+------+------+
 | abaW | W̊aba | abaŪ | Ūaba |
 *------+------+------+------*/

SELECT b'apple', TRIM(b'apple', b'na\xab') AS example

-- Note that the result of TRIM is of type BYTES, displayed as a base64-encoded string.
/*----------------------+------------------*
 | item                 | example          |
 +----------------------+------------------+
 | YXBwbGU=             | cHBsZQ==         |
 *----------------------+------------------*/

UNICODE(value)

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

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

UPPER(value)

SELECT UPPER('foo') AS example

/*---------*
 | example |
 +---------+
 | FOO     |
 *---------*/