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

CHARACTER_LENGTH(value)

SELECT
  'абвгд' AS characters,
  CHARACTER_LENGTH('абвгд') AS char_length_example

/*------------+---------------------*
 | characters | char_length_example |
 +------------+---------------------+
 | абвгд      |                   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== |
 *----------*/

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

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

COLLATE(value, collate_specification)

WITH Words AS (
  SELECT
    COLLATE('a', 'und:ci') AS char1,
    COLLATE('Z', 'und:ci') AS char2
)
SELECT ( Words.char1 < Words.char2 ) AS a_less_than_Z
FROM Words;

/*----------------*
 | a_less_than_Z  |
 +----------------+
 | TRUE           |
 *----------------*/

WITH Words AS (
  SELECT
    'a' AS char1,
    'Z' AS char2
)
SELECT ( Words.char1 < Words.char2 ) AS a_less_than_Z
FROM Words;

/*----------------*
 | a_less_than_Z  |
 +----------------+
 | FALSE          |
 *----------------*/

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

CONTAINS_SUBSTR(
  expression,
  search_value_literal
  [, json_scope => json_scope_value ]
)

SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result;

/*--------*
 | result |
 +--------+
 | true   |
 *--------*/

SELECT CONTAINS_SUBSTR('the blue house', CONCAT('Blue ', 'house')) AS result;

/*--------*
 | result |
 +--------+
 | true   |
 *--------*/

SELECT CONTAINS_SUBSTR('the red house', 'blue') AS result;

/*--------*
 | result |
 +--------+
 | false  |
 *--------*/

SELECT '\u2168 day' AS a, 'IX' AS b, CONTAINS_SUBSTR('\u2168', 'IX') AS result;

/*----------------------*
 | a      | b  | result |
 +----------------------+
 | Ⅸ day | IX | true   |
 *----------------------*/

SELECT CONTAINS_SUBSTR((23, 35, 41), '35') AS result;

/*--------*
 | result |
 +--------+
 | true   |
 *--------*/

SELECT CONTAINS_SUBSTR(('abc', ['def', 'ghi', 'jkl'], 'mno'), 'jk');

/*--------*
 | result |
 +--------+
 | true   |
 *--------*/

SELECT CONTAINS_SUBSTR((23, NULL, 41), '41') AS result;

/*--------*
 | result |
 +--------+
 | true   |
 *--------*/

SELECT CONTAINS_SUBSTR((23, NULL, 41), '35') AS result;

/*--------*
 | result |
 +--------+
 | null   |
 *--------*/

SELECT CONTAINS_SUBSTR('hello', NULL) AS result;
-- Throws an error

WITH Recipes AS
 (SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch, 'Potato dumplings' as Dinner UNION ALL
  SELECT 'Potato pancakes', 'Toasted cheese sandwich', 'Beef stroganoff' UNION ALL
  SELECT 'Ham scramble', 'Steak avocado salad', 'Tomato pasta' UNION ALL
  SELECT 'Avocado toast', 'Tomato soup', 'Blueberry salmon' UNION ALL
  SELECT 'Corned beef hash', 'Lentil potato soup', 'Glazed ham')
SELECT * FROM Recipes;

/*-------------------+-------------------------+------------------*
 | Breakfast         | Lunch                   | Dinner           |
 +-------------------+-------------------------+------------------+
 | Bluberry pancakes | Egg salad sandwich      | Potato dumplings |
 | Potato pancakes   | Toasted cheese sandwich | Beef stroganoff  |
 | Ham scramble      | Steak avocado salad     | Tomato pasta     |
 | Avocado toast     | Tomato soup             | Blueberry samon  |
 | Corned beef hash  | Lentil potato soup      | Glazed ham       |
 *-------------------+-------------------------+------------------*/

SELECT * FROM Recipes WHERE CONTAINS_SUBSTR(Recipes, 'toast');

/*-------------------+-------------------------+------------------*
 | Breakfast         | Lunch                   | Dinner           |
 +-------------------+-------------------------+------------------+
 | Potato pancakes   | Toasted cheese sandwich | Beef stroganoff  |
 | Avocado toast     | Tomato soup             | Blueberry samon  |
 *-------------------+-------------------------+------------------*/

SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch, Dinner), 'potato');

/*-------------------+-------------------------+------------------*
 | Breakfast         | Lunch                   | Dinner           |
 +-------------------+-------------------------+------------------+
 | Bluberry pancakes | Egg salad sandwich      | Potato dumplings |
 | Corned beef hash  | Lentil potato soup      | Glazed ham       |
 *-------------------+-------------------------+------------------*/

SELECT *
FROM Recipes
WHERE CONTAINS_SUBSTR(
  (SELECT AS STRUCT Recipes.* EXCEPT (Lunch, Dinner)),
  'potato'
);

/*-------------------+-------------------------+------------------*
 | Breakfast         | Lunch                   | Dinner           |
 +-------------------+-------------------------+------------------+
 | Potato pancakes   | Toasted cheese sandwich | Beef stroganoff  |
 *-------------------+-------------------------+------------------*/

SELECT CONTAINS_SUBSTR(JSON '{"lunch":"soup"}',"lunch") AS result;

/*--------*
 | result |
 +--------+
 | FALSE  |
 *--------*/

SELECT CONTAINS_SUBSTR(JSON '{"lunch":"soup"}',
                       "lunch",
                       json_scope=>"JSON_VALUES") AS result;

/*--------*
 | result |
 +--------+
 | FALSE  |
 *--------*/

SELECT CONTAINS_SUBSTR(JSON '{"lunch":"soup"}',
                       "lunch",
                       json_scope=>"JSON_KEYS_AND_VALUES") AS result;

/*--------*
 | result |
 +--------+
 | TRUE   |
 *--------*/

SELECT CONTAINS_SUBSTR(JSON '{"lunch":"soup"}',
                       "lunch",
                       json_scope=>"JSON_KEYS") AS result;

/*--------*
 | result |
 +--------+
 | TRUE   |
 *--------*/

EDIT_DISTANCE(
  value1,
  value2,
  [ max_distance => max_distance_value ]
)

SELECT EDIT_DISTANCE('a', 'b') AS results;

/*---------*
 | results |
 +---------+
 | 1       |
 *---------*/

SELECT EDIT_DISTANCE('aa', 'b') AS results;

/*---------*
 | results |
 +---------+
 | 2       |
 *---------*/

SELECT EDIT_DISTANCE('aa', 'ba') AS results;

/*---------*
 | results |
 +---------+
 | 1       |
 *---------*/

SELECT EDIT_DISTANCE('abcdefg', 'a', max_distance => 2) AS results;

/*---------*
 | results |
 +---------+
 | 2       |
 *---------*/

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)

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(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
  '[email protected]' AS email,
  REGEXP_CONTAINS('[email protected]', r'@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+') AS is_valid

/*-----------------+----------*
 | email           | is_valid |
 +-----------------+----------+
 | [email protected] | 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
  '[email protected]' AS email,
  REGEXP_CONTAINS('[email protected]', r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$') AS valid_email_address,
  REGEXP_CONTAINS('[email protected]', r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$') AS without_parentheses;

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | [email protected]      | true                | true                |
 *----------------+---------------------+---------------------*/

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

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | [email protected] | false               | true                |
 *----------------+---------------------+---------------------*/

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

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | [email protected]      | true                | true                |
 *----------------+---------------------+---------------------*/

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

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | [email protected]     | false               | true                |
 *----------------+---------------------+---------------------*/

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

/*----------------+---------------------+---------------------*
 | email          | valid_email_address | without_parentheses |
 +----------------+---------------------+---------------------+
 | [email protected]      | false               | false               |
 *----------------+---------------------+---------------------*/

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

SELECT REGEXP_EXTRACT('[email protected]', r'^[a-zA-Z0-9_.+-]+') AS user_name

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

SELECT REGEXP_EXTRACT('[email protected]', 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     |
 *-------------------------------------------*/

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

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

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

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)

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

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

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

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

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

STRPOS(value, subvalue)

SELECT STRPOS('[email protected]', '@') 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] |
 *---------+------------------------------------*/