String functions in GoogleSQL

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

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

CONCAT(value1[, ...])

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

/*---------------------*
 | author              |
 +---------------------+
 | T.P. Bar            |
 *---------------------*/

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)

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              | AK8=         |
 | 00010203aaeeefff | AAECA6ru7/8= |
 | 666f6f626172     | Zm9vYmFy     |
 *------------------+--------------*/

LCASE(val)

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

 ```googlesql
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's good practice to use parentheses around everything between `^`
and `$`.

```googlesql
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
  '