GoogleSQL for Spanner supports string functions.These string functions work on two different values:STRING andBYTES data types.STRING values must be well-formed UTF-8.

Functions that return position values, such asSTRPOS,encode those positions asINT64. The value1refers to the first character (or byte),2 refers to the second, and so on.The value0 indicates an invalid position. When working onSTRING types, thereturned positions refer to character positions.

All string comparisons are done byte-by-byte, without regard to Unicodecanonical equivalence.

Function list

BYTE_LENGTH

BYTE_LENGTH(value)

Description

Gets the number ofBYTES in aSTRING orBYTES value,regardless of whether the value is aSTRING orBYTES type.

Return type

INT64

Examples

SELECTBYTE_LENGTH('абвгд')ASstring_example;/*----------------+ | string_example | +----------------+ | 10             | +----------------*/

SELECTBYTE_LENGTH(b'абвгд')ASbytes_example;/*----------------+ | bytes_example  | +----------------+ | 10             | +----------------*/

CHAR_LENGTH

CHAR_LENGTH(value)

Description

Gets the number of characters in aSTRING value.

Return type

INT64

Examples

SELECTCHAR_LENGTH('абвгд')ASchar_length;/*-------------+ | char_length | +-------------+ | 5           | +------------ */

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

Description

Synonym forCHAR_LENGTH.

Return type

INT64

Examples

SELECT'абвгд'AScharacters,CHARACTER_LENGTH('абвгд')ASchar_length_example/*------------+---------------------+ | characters | char_length_example | +------------+---------------------+ | абвгд      |                   5 | +------------+---------------------*/

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_code_points)

Description

Takes an array of extended ASCIIcode pointsasARRAY<INT64> and returnsBYTES.

To convert fromBYTES to an array of code points, seeTO_CODE_POINTS.

Return type

BYTES

Examples

The following is a basic example usingCODE_POINTS_TO_BYTES.

SELECTCODE_POINTS_TO_BYTES([65,98,67,100])ASbytes;-- 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== | +----------*/

The following example uses a rotate-by-13 places (ROT13) algorithm to encode astring.

SELECTCODE_POINTS_TO_BYTES(ARRAY_AGG((SELECTCASEWHENchrBETWEENb'a'andb'z'THENTO_CODE_POINTS(b'a')[offset(0)]+MOD(code+13-TO_CODE_POINTS(b'a')[offset(0)],26)WHENchrBETWEENb'A'andb'Z'THENTO_CODE_POINTS(b'A')[offset(0)]+MOD(code+13-TO_CODE_POINTS(b'A')[offset(0)],26)ELSEcodeENDFROM(SELECTcode,CODE_POINTS_TO_BYTES([code])chr))ORDERBYOFFSET))ASencoded_stringFROMUNNEST(TO_CODE_POINTS(b'Test String!'))codeWITHOFFSET;-- 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

CODE_POINTS_TO_STRING(unicode_code_points)

Description

Takes an array of Unicodecode pointsasARRAY<INT64> and returns aSTRING.

To convert from a string to an array of code points, seeTO_CODE_POINTS.

Return type

STRING

Examples

The following are basic examples usingCODE_POINTS_TO_STRING.

SELECTCODE_POINTS_TO_STRING([65,255,513,1024])ASstring;/*--------+ | string | +--------+ | AÿȁЀ   | +--------*/

SELECTCODE_POINTS_TO_STRING([97,0,0xF9B5])ASstring;/*--------+ | string | +--------+ | a例    | +--------*/

SELECTCODE_POINTS_TO_STRING([65,255,NULL,1024])ASstring;/*--------+ | string | +--------+ | NULL   | +--------*/

The following example computes the frequency of letters in a set of words.

WITHWordsAS(SELECTwordFROMUNNEST(['foo','bar','baz','giraffe','llama'])ASword)SELECTCODE_POINTS_TO_STRING([code_point])ASletter,COUNT(*)ASletter_countFROMWords,UNNEST(TO_CODE_POINTS(word))AScode_pointGROUPBY1ORDERBY2DESC;/*--------+--------------+ | 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

CONCAT(value1[,...])

Description

Concatenates one or moreSTRING orBYTE values into a single result.

The function returnsNULL if any input argument isNULL.

Return type

STRING orBYTES

Examples

SELECTCONCAT('T.P.',' ','Bar')asauthor;/*---------------------+ | author              | +---------------------+ | T.P. Bar            | +---------------------*/

WithEmployeesAS(SELECT'John'ASfirst_name,'Doe'ASlast_nameUNIONALLSELECT'Jane'ASfirst_name,'Smith'ASlast_nameUNIONALLSELECT'Joe'ASfirst_name,'Jackson'ASlast_name)SELECTCONCAT(first_name,' ',last_name)ASfull_nameFROMEmployees;/*---------------------+ | full_name           | +---------------------+ | John Doe            | | Jane Smith          | | Joe Jackson         | +---------------------*/

ENDS_WITH

ENDS_WITH(value,suffix)

Description

Takes twoSTRING orBYTES values. ReturnsTRUE ifsuffixis a suffix ofvalue.

Return type

BOOL

Examples

SELECTENDS_WITH('apple','e')asexample/*---------+ | example | +---------+ |    True | +---------*/

FORMAT

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

Description

FORMAT formats a data type expression as a string.

• format_string_expression: Can contain zero or moreformat specifiers. Each format specifier is introducedby the% symbol, and must map to one or more of the remaining arguments.In general, this is a one-to-one mapping, except when the* specifier ispresent. For example,%.*i maps to two arguments—a length argumentand a signed integer argument. If the number of arguments related to theformat specifiers isn't the same as the number of arguments, an error occurs.
• data_type_expression: The value to format as a string. This can be anyGoogleSQL data type.

Return type

STRING

Examples

TheFORMAT() function doesn't provide fully customizable formatting for alltypes and values, nor formatting that's sensitive to locale.

If custom formatting is necessary for a type, you must first format it usingtype-specific format functions, such asFORMAT_DATE() orFORMAT_TIMESTAMP().For example:

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

Returns

date:January02,2015!

Supported format specifiers

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

Aformat specifier adds formatting when casting avalue to a string. It can optionally contain these sub-specifiers:

• Flags
• Width
• Precision

Additional information about format specifiers:

• %g and %G behavior
• %p and %P behavior
• %t and %T behavior
• Error conditions
• NULL argument handling
• Additional semantic rules

Format specifiers

year: 2019 month: 10

{"month":10,"year":2019}

year: 2019month: 10

{  "month": 10,  "year": 2019}

The format specifier can optionally contain the sub-specifiers identified abovein the specifier prototype.

These sub-specifiers must comply with the following specifications.

Flags

Flags may be specified in any order. Duplicate flags aren't an error. Whenflags aren't relevant for some element type, they are ignored.

Width

Precision

%g and %G behavior

The%g and%G format specifiers choose either the decimal notation (likethe%f and%F specifiers) or the scientific notation (like the%e and%Especifiers), depending on the input value's exponent and the specifiedprecision.

Let p stand for the specifiedprecision (defaults to 6; 1 if thespecified precision is less than 1). The input value is first converted toscientific notation with precision = (p - 1). If the resulting exponent part xis less than -4 or no less than p, the scientific notation with precision =(p - 1) is used; otherwise the decimal notation with precision = (p - 1 - x) isused.

Unless# flag is present, the trailing zeros after the decimal pointare removed, and the decimal point is also removed if there is no digit afterit.

%p and %P behavior

The%p format specifier produces a one-line printable string. The%Pformat specifier produces a multi-line printable string. You can use theseformat specifiers with the following data types:

message ReleaseDate { required int32 year = 1 [default=2019]; required int32 month = 2 [default=10];}

year: 2019 month: 10

message ReleaseDate { required int32 year = 1 [default=2019]; required int32 month = 2 [default=10];}

year: 2019month: 10

JSON '{  "month": 10,  "year": 2019}'

{"month":10,"year":2019}

JSON '{  "month": 10,  "year": 2019}'

{  "month": 10,  "year": 2019}

%t and %T behavior

The%t and%T format specifiers are defined for all types. Thewidth,precision, andflags act as they dofor%s: thewidth is the minimum width and theSTRING will bepadded to that size, andprecision is the maximum widthof content to show and theSTRING will be truncated to that size, prior topadding to width.

The%t specifier is always meant to be a readable form of the value.

The%T specifier is always a valid SQL literal of a similar type, such as awider numeric type. The literal will not include casts or a type name,except for the special case of non-finite floating point values.

TheSTRING is formatted as follows:

{"name":"apple","stock":3}

JSON'{"name":"apple","stock":3}'

Error conditions

If a format specifier is invalid, or isn't compatible with the relatedargument type, or the wrong number or arguments are provided, then an error isproduced. For example, the following<format_string> expressions are invalid:

FORMAT('%s',1)

FORMAT('%')

NULL argument handling

ANULL format string results in aNULL outputSTRING. Any other argumentsare ignored in this case.

The function generally produces aNULL value if aNULL argument is present.For example,FORMAT('%i', NULL_expression) produces aNULL STRING asoutput.

However, there are some exceptions: if the format specifier is %t or %T(both of which produceSTRINGs that effectively match CAST and literal valuesemantics), aNULL value produces 'NULL' (without the quotes) in the resultSTRING. For example, the function:

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

Returns

00-NULL-00

Additional semantic rules

FLOAT64 andFLOAT32 values can be+/-inf orNaN.When an argument has one of those values, the result of the format specifiers%f,%F,%e,%E,%g,%G, and%t areinf,-inf, ornan(or the same in uppercase) as appropriate. This is consistent with howGoogleSQL casts these values toSTRING. For%T,GoogleSQL returns quoted strings forFLOAT64 values that don't have non-string literalrepresentations.

FROM_BASE32

FROM_BASE32(string_expr)

Description

Converts the base32-encoded inputstring_expr intoBYTES format. To convertBYTES to a base32-encodedSTRING, useTO_BASE32.

Return type

BYTES

Example

SELECTFROM_BASE32('MFRGGZDF74======')ASbyte_data;-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string./*-----------+ | byte_data | +-----------+ | YWJjZGX/  | +-----------*/

FROM_BASE64

FROM_BASE64(string_expr)

Description

Converts the base64-encoded inputstring_expr intoBYTES format. To convertBYTES to a base64-encodedSTRING,useTO_BASE64.

There are several base64 encodings in common use that vary in exactly whichalphabet of 65 ASCII characters are used to encode the 64 digits and padding.SeeRFC 4648 for details. Thisfunction expects the alphabet[A-Za-z0-9+/=].

Return type

BYTES

Example

SELECTFROM_BASE64('/+A=')ASbyte_data;-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string./*-----------+ | byte_data | +-----------+ | /+A=      | +-----------*/

To work with an encoding using a different base64 alphabet, you might need tocomposeFROM_BASE64 with theREPLACE function. For instance, thebase64url url-safe and filename-safe encoding commonly used in web programminguses-_= as the last characters rather than+/=. To decode abase64url-encoded string, replace- and_ with+ and/ respectively.

SELECTFROM_BASE64(REPLACE(REPLACE('_-A=','-','+'),'_','/'))ASbinary;-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string./*--------+ | binary | +--------+ | /+A=   | +--------*/

FROM_HEX

FROM_HEX(string)

Description

Converts a hexadecimal-encodedSTRING intoBYTES format. Returns an errorif the inputSTRING contains characters outside the range(0..9, A..F, a..f). The lettercase of the characters doesn't matter. If theinputSTRING has an odd number of characters, the function acts as if theinput has an additional leading0. To convertBYTES to a hexadecimal-encodedSTRING, useTO_HEX.

Return type

BYTES

Example

WITHInputAS(SELECT'00010203aaeeefff'AShex_strUNIONALLSELECT'0AF'UNIONALLSELECT'666f6f626172')SELECThex_str,FROM_HEX(hex_str)ASbytes_strFROMInput;-- 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

LCASE(val)

Alias forLOWER.

LENGTH

LENGTH(value)

Description

Returns the length of theSTRING orBYTES value. The returnedvalue is in characters forSTRING arguments and in bytes for theBYTESargument.

Return type

INT64

Examples

SELECTLENGTH('абвгд')ASstring_example,LENGTH(CAST('абвгд'ASBYTES))ASbytes_example;/*----------------+---------------+ | string_example | bytes_example | +----------------+---------------+ | 5              | 10            | +----------------+---------------*/

LOWER

LOWER(value)

Description

ForSTRING arguments, returns the original string with all alphabeticcharacters in lowercase. Mapping between lowercase and uppercase is doneaccording to theUnicode Character Databasewithout taking into account language-specific mappings.

ForBYTES arguments, the argument is treated as ASCII text, with all bytesgreater than 127 left intact.

Return type

STRING orBYTES

Examples

SELECTLOWER('FOO BAR BAZ')ASexampleFROMitems;/*-------------+ | example     | +-------------+ | foo bar baz | +-------------*/

LPAD

LPAD(original_value,return_length[,pattern])

Description

Returns aSTRING orBYTES value that consists oforiginal_value prependedwithpattern. Thereturn_length is anINT64 thatspecifies the length of the returned value. Iforiginal_value is of typeBYTES,return_length is the number of bytes. Iforiginal_value isof typeSTRING,return_length is the number of characters.

The default value ofpattern is a blank space.

Bothoriginal_value andpattern must be the same data type.

Ifreturn_length is less than or equal to theoriginal_value length, thisfunction returns theoriginal_value value, truncated to the value ofreturn_length. For example,LPAD('hello world', 7); returns'hello w'.

Iforiginal_value,return_length, orpattern isNULL, this functionreturnsNULL.

This function returns an error if:

• return_length is negative
• pattern is empty

Return type

STRING orBYTES

Examples

SELECTFORMAT('%T',LPAD('c',5))ASresults/*---------+ | results | +---------+ | "    c" | +---------*/

SELECTLPAD('b',5,'a')ASresults/*---------+ | results | +---------+ | aaaab   | +---------*/

SELECTLPAD('abc',10,'ghd')ASresults/*------------+ | results    | +------------+ | ghdghdgabc | +------------*/

SELECTLPAD('abc',2,'d')ASresults/*---------+ | results | +---------+ | ab      | +---------*/

SELECTFORMAT('%T',LPAD(b'abc',10,b'ghd'))ASresults/*---------------+ | results       | +---------------+ | b"ghdghdgabc" | +---------------*/

LTRIM

LTRIM(value1[,value2])

Description

Identical toTRIM, but only removes leading characters.

Return type

STRING orBYTES

Examples

SELECTCONCAT('#',LTRIM('   apple   '),'#')ASexample/*-------------+ | example     | +-------------+ | #apple   #  | +-------------*/

SELECTLTRIM('***apple***','*')ASexample/*-----------+ | example   | +-----------+ | apple***  | +-----------*/

SELECTLTRIM('xxxapplexxx','xyz')ASexample/*-----------+ | example   | +-----------+ | applexxx  | +-----------*/

NORMALIZE

NORMALIZE(value[,normalization_mode])

Description

Takes a string value and returns it as a normalized string. If you don'tprovide a normalization mode,NFC is used.

Normalization is used to ensure thattwo strings are equivalent. Normalization is often used in situations in whichtwo strings render the same on the screen but have different Unicode codepoints.

NORMALIZE supports four optional normalization modes:

Return type

STRING

Examples

The following example normalizes different language characters:

SELECTNORMALIZE('\u00ea')asa,NORMALIZE('\u0065\u0302')asb,NORMALIZE('\u00ea')=NORMALIZE('\u0065\u0302')asnormalized;/*---+---+------------+ | a | b | normalized | +---+---+------------+ | ê | ê | TRUE       | +---+---+------------*/

The following examples normalize different space characters:

SELECTNORMALIZE('Raha\u2004Mahan',NFKC)ASnormalized_name/*-----------------+ | normalized_name | +-----------------+ | Raha Mahan      | +-----------------*/

SELECTNORMALIZE('Raha\u2005Mahan',NFKC)ASnormalized_name/*-----------------+ | normalized_name | +-----------------+ | Raha Mahan      | +-----------------*/

SELECTNORMALIZE('Raha\u2006Mahan',NFKC)ASnormalized_name/*-----------------+ | normalized_name | +-----------------+ | Raha Mahan      | +-----------------*/

SELECTNORMALIZE('Raha Mahan',NFKC)ASnormalized_name/*-----------------+ | normalized_name | +-----------------+ | Raha Mahan      | +-----------------*/

NORMALIZE_AND_CASEFOLD

NORMALIZE_AND_CASEFOLD(value[,normalization_mode])

Description

Takes a string value and returns it as a normalized string. If you don'tprovide a normalization mode,NFC is used.

Normalization is used to ensure thattwo strings are equivalent. Normalization is often used in situations in whichtwo strings render the same on the screen but have different Unicode codepoints.

Case folding is used for the caselesscomparison of strings. If you need to compare strings and case shouldn't beconsidered, useNORMALIZE_AND_CASEFOLD, otherwise useNORMALIZE.

NORMALIZE_AND_CASEFOLD supports four optional normalization modes:

Return type

STRING

Examples

SELECTNORMALIZE('The red barn')=NORMALIZE('The Red Barn')ASnormalized,NORMALIZE_AND_CASEFOLD('The red barn')=NORMALIZE_AND_CASEFOLD('The Red Barn')ASnormalized_with_case_folding;/*------------+------------------------------+ | normalized | normalized_with_case_folding | +------------+------------------------------+ | FALSE      | TRUE                         | +------------+------------------------------*/

SELECT'\u2168'ASa,'IX'ASb,NORMALIZE_AND_CASEFOLD('\u2168',NFD)=NORMALIZE_AND_CASEFOLD('IX',NFD)ASnfd,NORMALIZE_AND_CASEFOLD('\u2168',NFC)=NORMALIZE_AND_CASEFOLD('IX',NFC)ASnfc,NORMALIZE_AND_CASEFOLD('\u2168',NFKD)=NORMALIZE_AND_CASEFOLD('IX',NFKD)ASnfkd,NORMALIZE_AND_CASEFOLD('\u2168',NFKC)=NORMALIZE_AND_CASEFOLD('IX',NFKC)ASnfkc;/*---+----+-------+-------+------+------+ | a | b  | nfd   | nfc   | nfkd | nfkc | +---+----+-------+-------+------+------+ | Ⅸ | IX | false | false | true | true | +---+----+-------+-------+------+------*/

SELECT'\u0041\u030A'ASa,'\u00C5'ASb,NORMALIZE_AND_CASEFOLD('\u0041\u030A',NFD)=NORMALIZE_AND_CASEFOLD('\u00C5',NFD)ASnfd,NORMALIZE_AND_CASEFOLD('\u0041\u030A',NFC)=NORMALIZE_AND_CASEFOLD('\u00C5',NFC)ASnfc,NORMALIZE_AND_CASEFOLD('\u0041\u030A',NFKD)=NORMALIZE_AND_CASEFOLD('\u00C5',NFKD)ASnkfd,NORMALIZE_AND_CASEFOLD('\u0041\u030A',NFKC)=NORMALIZE_AND_CASEFOLD('\u00C5',NFKC)ASnkfc;/*---+----+-------+-------+------+------+ | a | b  | nfd   | nfc   | nkfd | nkfc | +---+----+-------+-------+------+------+ | Å | Å  | true  | true  | true | true | +---+----+-------+-------+------+------*/

OCTET_LENGTH

OCTET_LENGTH(value)

Alias forBYTE_LENGTH.

REGEXP_CONTAINS

REGEXP_CONTAINS(value,regexp)

Description

ReturnsTRUE ifvalue is a partial match for the regular expression,regexp.

If theregexp argument is invalid, the function returns an error.

You can search for a full match by using^ (beginning of text) and$ (end oftext). Due to regular expression operator precedence, it's good practice to useparentheses around everything between^ and$.

Return type

BOOL

Examples

The following queries check to see if an email is valid:

SELECT'foo@example.com'ASemail,REGEXP_CONTAINS('foo@example.com',r'@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+')ASis_valid/*-----------------+----------+ | email           | is_valid | +-----------------+----------+ | foo@example.com | TRUE     | +-----------------+----------*/``````googlesqlSELECT'www.example.net'ASemail,REGEXP_CONTAINS('www.example.net',r'@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+')ASis_valid/*-----------------+----------+ | email           | is_valid | +-----------------+----------+ | www.example.net | FALSE    | +-----------------+----------*/```The following queries check to see if an email is valid. Theyperform a full match, using `^` and `$`. Due to regular expression operatorprecedence, it's good practice to use parentheses around everything between `^`and `$`.```googlesqlSELECT'a@foo.com'ASemail,REGEXP_CONTAINS('a@foo.com',r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$')ASvalid_email_address,REGEXP_CONTAINS('a@foo.com',r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$')ASwithout_parentheses;/*----------------+---------------------+---------------------+ | email          | valid_email_address | without_parentheses | +----------------+---------------------+---------------------+ | a@foo.com      | true                | true                | +----------------+---------------------+---------------------*/

SELECT'a@foo.computer'ASemail,REGEXP_CONTAINS('a@foo.computer',r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$')ASvalid_email_address,REGEXP_CONTAINS('a@foo.computer',r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$')ASwithout_parentheses;/*----------------+---------------------+---------------------+ | email          | valid_email_address | without_parentheses | +----------------+---------------------+---------------------+ | a@foo.computer | false               | true                | +----------------+---------------------+---------------------*/

SELECT'b@bar.org'ASemail,REGEXP_CONTAINS('b@bar.org',r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$')ASvalid_email_address,REGEXP_CONTAINS('b@bar.org',r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$')ASwithout_parentheses;/*----------------+---------------------+---------------------+ | email          | valid_email_address | without_parentheses | +----------------+---------------------+---------------------+ | b@bar.org      | true                | true                | +----------------+---------------------+---------------------*/

SELECT'!b@bar.org'ASemail,REGEXP_CONTAINS('!b@bar.org',r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$')ASvalid_email_address,REGEXP_CONTAINS('!b@bar.org',r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$')ASwithout_parentheses;/*----------------+---------------------+---------------------+ | email          | valid_email_address | without_parentheses | +----------------+---------------------+---------------------+ | !b@bar.org     | false               | true                | +----------------+---------------------+---------------------*/

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

REGEXP_EXTRACT

REGEXP_EXTRACT(value,regexp)

Description

Returns the first substring invalue that matches there2 regular expression,regexp. ReturnsNULL if there is no match.

If the regular expression contains a capturing group ((...)), and there is amatch for that capturing group, that match is returned. If thereare multiple matches for a capturing group, the first match is returned.

Returns an error if:

• The regular expression is invalid
• The regular expression has more than one capturing group

Return type

STRING orBYTES

Examples

SELECTREGEXP_EXTRACT('foo@example.com',r'^[a-zA-Z0-9_.+-]+')ASuser_name/*-----------+ | user_name | +-----------+ | foo       | +-----------*/

SELECTREGEXP_EXTRACT('foo@example.com',r'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)')/*------------------+ | top_level_domain | +------------------+ | com              | +------------------*/

SELECTREGEXP_EXTRACT('ab','.b')ASresult_a,REGEXP_EXTRACT('ab','(.)b')ASresult_b,REGEXP_EXTRACT('xyztb','(.)+b')ASresult_c,REGEXP_EXTRACT('ab','(z)?b')ASresult_d/*-------------------------------------------+ | result_a | result_b | result_c | result_d | +-------------------------------------------+ | ab       | a        | t        | NULL     | +-------------------------------------------*/

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value,regexp)

Description

Returns an array of all substrings ofvalue that match there2 regular expression,regexp. Returns an empty arrayif there is no match.

If the regular expression contains a capturing group ((...)), and there is amatch for that capturing group, that match is added to the results.

TheREGEXP_EXTRACT_ALL function only returns non-overlapping matches. Forexample, using this function to extractana frombanana returns only onesubstring, not two.

Returns an error if:

• The regular expression is invalid
• The regular expression has more than one capturing group

Return type

ARRAY<STRING> orARRAY<BYTES>

Examples

SELECTREGEXP_EXTRACT_ALL('Try `func(x)` or `func(y)`','`(.+?)`')ASexample/*--------------------+ | example            | +--------------------+ | [func(x), func(y)] | +--------------------*/

REGEXP_REPLACE

REGEXP_REPLACE(value,regexp,replacement)

Description

Returns aSTRING where all substrings ofvalue thatmatch regular expressionregexp are replaced withreplacement.

You can use backslashed-escaped digits (\1 to \9) within thereplacementargument to insert text matching the corresponding parenthesized group in theregexp pattern. Use \0 to refer to the entire matching text.

To add a backslash in your regular expression, you must first escape it. Forexample,SELECT REGEXP_REPLACE('abc', 'b(.)', 'X\\1'); returnsaXc. You canalso useraw strings to remove one layer ofescaping, for exampleSELECT REGEXP_REPLACE('abc', 'b(.)', r'X\1');.

TheREGEXP_REPLACE function only replaces non-overlapping matches. Forexample, replacingana withinbanana results in only one replacement, nottwo.

If theregexp argument isn't a valid regular expression, this functionreturns an error.

Return type

STRING orBYTES

Examples

SELECTREGEXP_REPLACE('# Heading',r'^# ([a-zA-Z0-9\s]+$)','<h1>\\1</h1>')AShtml/*--------------------------+ | html                     | +--------------------------+ | <h1>Heading</h1>         | +--------------------------*/

REPEAT

REPEAT(original_value,repetitions)

Description

Returns aSTRING orBYTES value that consists oforiginal_value, repeated.Therepetitions parameter specifies the number of times to repeatoriginal_value. ReturnsNULL if eitheroriginal_value orrepetitionsareNULL.

This function returns an error if therepetitions value is negative.

Return type

STRING orBYTES

Examples

SELECTREPEAT('abc',3)ASresults/*-----------+ | results   | |-----------| | abcabcabc | +-----------*/

SELECTREPEAT('abc',NULL)ASresults/*---------+ | results | |---------| | NULL    | +---------*/

SELECTREPEAT(NULL,3)ASresults/*---------+ | results | |---------| | NULL    | +---------*/

REPLACE

REPLACE(original_value,from_pattern,to_pattern)

Description

Replaces all occurrences offrom_pattern withto_pattern inoriginal_value. Iffrom_pattern is empty, no replacement is made.

Return type

STRING orBYTES

Examples

WITHdessertsAS(SELECT'apple pie'asdessertUNIONALLSELECT'blackberry pie'asdessertUNIONALLSELECT'cherry pie'asdessert)SELECTREPLACE(dessert,'pie','cobbler')asexampleFROMdesserts;/*--------------------+ | example            | +--------------------+ | apple cobbler      | | blackberry cobbler | | cherry cobbler     | +--------------------*/

REVERSE

REVERSE(value)

Description

Returns the reverse of the inputSTRING orBYTES.

Return type

STRING orBYTES

Examples

SELECTREVERSE('abc')ASresults/*---------+ | results | +---------+ | cba     | +---------*/

SELECTFORMAT('%T',REVERSE(b'1a3'))ASresults/*---------+ | results | +---------+ | b"3a1"  | +---------*/

RPAD

RPAD(original_value,return_length[,pattern])

Description

Returns aSTRING orBYTES value that consists oforiginal_value appendedwithpattern. Thereturn_length parameter is anINT64 that specifies the length of thereturned value. Iforiginal_value isBYTES,return_length is the number of bytes. Iforiginal_value isSTRING,return_length is the number of characters.

The default value ofpattern is a blank space.

Bothoriginal_value andpattern must be the same data type.

Ifreturn_length is less than or equal to theoriginal_value length, thisfunction returns theoriginal_value value, truncated to the value ofreturn_length. For example,RPAD('hello world', 7); returns'hello w'.

Iforiginal_value,return_length, orpattern isNULL, this functionreturnsNULL.

This function returns an error if:

• return_length is negative
• pattern is empty

Return type

STRING orBYTES

Examples

SELECTFORMAT('%T',RPAD('c',5))ASresults/*---------+ | results | +---------+ | "c    " | +---------*/

SELECTRPAD('b',5,'a')ASresults/*---------+ | results | +---------+ | baaaa   | +---------*/

SELECTRPAD('abc',10,'ghd')ASresults/*------------+ | results    | +------------+ | abcghdghdg | +------------*/

SELECTRPAD('abc',2,'d')ASresults/*---------+ | results | +---------+ | ab      | +---------*/

SELECTFORMAT('%T',RPAD(b'abc',10,b'ghd'))ASresults/*---------------+ | results       | +---------------+ | b"abcghdghdg" | +---------------*/

RTRIM

RTRIM(value1[,value2])

Description

Identical toTRIM, but only removes trailing characters.

Return type

STRING orBYTES

Examples

SELECTRTRIM('***apple***','*')ASexample/*-----------+ | example   | +-----------+ | ***apple  | +-----------*/

SELECTRTRIM('applexxz','xyz')ASexample/*---------+ | example | +---------+ | apple   | +---------*/

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

Description

Converts a sequence ofBYTES to aSTRING. Any invalid UTF-8 characters arereplaced with the Unicode replacement character,U+FFFD.

Return type

STRING

Examples

The following statement returns the Unicode replacement character, �.

SELECTSAFE_CONVERT_BYTES_TO_STRING(b'\xc2')assafe_convert;

SOUNDEX

SOUNDEX(value)

Description

Returns aSTRING that represents theSoundex code forvalue.

SOUNDEX produces a phonetic representation of a string. It indexes words bysound, as pronounced in English. It's typically used to help determine whethertwo strings, such as the family namesLevine andLavine, or the wordstoandtoo, have similar English-language pronunciation.

The result of the SOUNDEX consists of a letter followed by 3 digits. Non-latincharacters are ignored. If the remaining string is empty after removingnon-Latin characters, an emptySTRING is returned.

Return type

STRING

Examples

SELECT'Ashcraft'ASvalue,SOUNDEX('Ashcraft')ASsoundex/*----------------------+---------+ | value                | soundex | +----------------------+---------+ | Ashcraft             | A261    | +----------------------+---------*/

SPLIT

SPLIT(value[,delimiter])

Description

Splits aSTRING orBYTES value, using a delimiter. Thedelimiter argumentmust be a literal character or sequence of characters. You can't split with aregular expression.

ForSTRING, the default delimiter is the comma,.

ForBYTES, you must specify a delimiter.

Splitting on an empty delimiter produces an array of UTF-8 characters forSTRING values, and an array ofBYTES forBYTES values.

Splitting an emptySTRING returns anARRAY with a single emptySTRING.

Return type

ARRAY<STRING> orARRAY<BYTES>

Examples

WITHlettersAS(SELECT''asletter_groupUNIONALLSELECT'a'asletter_groupUNIONALLSELECT'b c d'asletter_group)SELECTSPLIT(letter_group,' ')asexampleFROMletters;/*----------------------+ | example              | +----------------------+ | []                   | | [a]                  | | [b, c, d]            | +----------------------*/

SPLIT_SUBSTR

SPLIT_SUBSTR(value,delimiter,start_split[,count])

Description

Returns a substring from an inputSTRING that's determined by a delimiter, alocation that indicates the first split of the substring to return, and thenumber of splits to include in the returned substring.

Thevalue argument is the suppliedSTRING value from which a substring isreturned.

Thedelimiter argument is the delimiter used to split the inputSTRING. Itmust be a literal character or sequence of characters.

• Thedelimiter argument can't be a regular expression.
• Delimiter matching is from left to right.
• If the delimiter is a sequence of characters, then two instances of thedelimiter in the input string can't overlap. For example, if the delimiter is**, then the delimiters in the stringaa***bb***cc are:
  • The first two asterisks afteraa.
  • The first two asterisks afterbb.

Thestart_split argument is an integer that specifies the first split of thesubstring to return.

• Ifstart_split is1, then the returned substring starts from the firstsplit.
• Ifstart_split is0 or less than the negative of the number of splits,thenstart_split is treated as if it's1 and returns a substring thatstarts with the first split.
• Ifstart_split is greater than the number of splits, then an empty string isreturned.
• Ifstart_split is negative, then the splits are counted from the end of theinput string. Ifstart_split is-1, then the last split in the inputstring is returned.

The optionalcount argument is an integer that specifies the maximum numberof splits to include in the returned substring.

• Ifcount isn't specified, then the substring from thestart_splitposition to the end of the input string is returned.
• Ifcount is0, an empty string is returned.
• Ifcount is negative, an error is returned.
• If the sum ofcount plusstart_split is greater than the number of splits,then a substring fromstart_split to the end of the input string isreturned.

Return type

STRING

Examples

The following example returns an empty string becausecount is0:

SELECTSPLIT_SUBSTR("www.abc.xyz.com",".",1,0)ASexample/*---------+ | example | +---------+ |         | +---------*/

The following example returns two splits starting with the first split:

SELECTSPLIT_SUBSTR("www.abc.xyz.com",".",1,2)ASexample/*---------+ | example | +---------+ | www.abc | +---------*/

The following example returns one split starting with the first split:

SELECTSPLIT_SUBSTR("www.abc.xyz.com",".",1,1)ASexample/*---------+ | example | +---------+ | www     | +---------*/

The following example returns splits from the right becausestart_split is anegative value:

SELECTSPLIT_SUBSTR("www.abc.xyz.com",".",-1,1)ASexample/*---------+ | example | +---------+ | com     | +---------*/

The following example returns a substring with three splits, starting with thefirst split:

SELECTSPLIT_SUBSTR("www.abc.xyz.com",".",1,3)ASexample/*-------------+ | example     | +-------------+ | www.abc.xyz | +------------*/

Ifstart_split is zero, then it's treated as if it's1. The followingexample returns three substrings starting with the first split:

SELECTSPLIT_SUBSTR("www.abc.xyz.com",".",0,3)ASexample/*-------------+ | example     | +-------------+ | www.abc.xyz | +------------*/

Ifstart_split is greater than the number of splits, then an empty string isreturned:

SELECTSPLIT_SUBSTR("www.abc.xyz.com",".",5,3)ASexample/*---------+ | example | +---------+ |         | +--------*/

In the following example, thestart_split value (-5) is less than thenegative of the number of splits (-4), sostart_split is treated as1:

SELECTSPLIT_SUBSTR("www.abc.xyz.com",".",-5,3)ASexample/*-------------+ | example     | +-------------+ | www.abc.xyz | +------------*/

In the following example, the substring fromstart_split to the end of thestring is returned becausecount isn't specified:

SELECTSPLIT_SUBSTR("www.abc.xyz.com",".",3)ASexample/*---------+ | example | +---------+ | xyz.com | +--------*/

The following two examples demonstrate howSPLIT_SUBSTR works with amulti-character delimiter that has overlapping matches in the input string. Ineach example, the input string contains instances of three asterisks in a row(***) and the delimiter is two asterisks (**).

SELECTSPLIT_SUBSTR('aaa***bbb***ccc','**',1,2)ASexample/*-----------+ | example   | +-----------+ | aaa***bbb | +----------*/

SELECTSPLIT_SUBSTR('aaa***bbb***ccc','**',2,2)ASexample/*------------+ | example    | +------------+ | *bbb***ccc | +-----------*/

STARTS_WITH

STARTS_WITH(value,prefix)

Description

Takes twoSTRING orBYTES values. ReturnsTRUE ifprefix is aprefix ofvalue.

Return type

BOOL

Examples

SELECTSTARTS_WITH('bar','b')ASexample/*---------+ | example | +---------+ |    True | +---------*/

STRPOS

STRPOS(value,subvalue)

Description

Takes twoSTRING orBYTES values. Returns the 1-based position of the firstoccurrence ofsubvalue insidevalue. Returns0 ifsubvalue isn't found.

Return type

INT64

Examples

SELECTSTRPOS('foo@example.com','@')ASexample/*---------+ | example | +---------+ |       4 | +---------*/

SUBSTR

SUBSTR(value,position[,length])

Description

Gets a portion (substring) of the suppliedSTRING orBYTES value.

Theposition argument is an integer specifying the starting position of thesubstring.

• Ifposition is1, the substring starts from the first character or byte.
• Ifposition is0 or less than-LENGTH(value),position is set to1,and the substring starts from the first character or byte.
• Ifposition is greater than the length ofvalue, the function producesan empty substring.
• Ifposition is negative, the function counts from the end ofvalue,with-1 indicating the last character or byte.

Thelength argument specifies the maximum number of characters or bytes toreturn.

• Iflength isn't specified, the function produces a substring that startsat the specified position and ends at the last character or byte ofvalue.
• Iflength is0, the function produces an empty substring.
• Iflength is negative, the function produces an error.
• The returned substring may be shorter thanlength, for example, whenlength exceeds the length ofvalue, or when the starting position of thesubstring pluslength is greater than the length ofvalue.

Return type

STRING orBYTES

Examples

SELECTSUBSTR('apple',2)ASexample/*---------+ | example | +---------+ | pple    | +---------*/

SELECTSUBSTR('apple',2,2)ASexample/*---------+ | example | +---------+ | pp      | +---------*/

SELECTSUBSTR('apple',-2)ASexample/*---------+ | example | +---------+ | le      | +---------*/

SELECTSUBSTR('apple',1,123)ASexample/*---------+ | example | +---------+ | apple   | +---------*/

SELECTSUBSTR('apple',123)ASexample/*---------+ | example | +---------+ |         | +---------*/

SELECTSUBSTR('apple',123,5)ASexample/*---------+ | example | +---------+ |         | +---------*/

SUBSTRING

SUBSTRING(value,position[,length])

Alias forSUBSTR.

TO_BASE32

TO_BASE32(bytes_expr)

Description

Converts a sequence ofBYTES into a base32-encodedSTRING. To convert abase32-encodedSTRING intoBYTES, useFROM_BASE32.

Return type

STRING

Example

SELECTTO_BASE32(b'abcde\xFF')ASbase32_string;/*------------------+ | base32_string    | +------------------+ | MFRGGZDF74====== | +------------------*/

TO_BASE64

TO_BASE64(bytes_expr)

Description

Converts a sequence ofBYTES into a base64-encodedSTRING. To convert abase64-encodedSTRING intoBYTES, useFROM_BASE64.

There are several base64 encodings in common use that vary in exactly whichalphabet of 65 ASCII characters are used to encode the 64 digits and padding.SeeRFC 4648 for details. Thisfunction adds padding and uses the alphabet[A-Za-z0-9+/=].

Return type

STRING

Example

SELECTTO_BASE64(b'\377\340')ASbase64_string;/*---------------+ | base64_string | +---------------+ | /+A=          | +---------------*/

To work with an encoding using a different base64 alphabet, you might need tocomposeTO_BASE64 with theREPLACE function. For instance, thebase64url url-safe and filename-safe encoding commonly used in web programminguses-_= as the last characters rather than+/=. To encode abase64url-encoded string, replace+ and/ with- and_ respectively.

SELECTREPLACE(REPLACE(TO_BASE64(b'\377\340'),'+','-'),'/','_')aswebsafe_base64;/*----------------+ | websafe_base64 | +----------------+ | _-A=           | +----------------*/

TO_CODE_POINTS

TO_CODE_POINTS(value)

Description

Takes aSTRING orBYTES value and returns an array ofINT64 values thatrepresent code points or extended ASCII character values.

• Ifvalue is aSTRING, each element in the returned array represents acode point. Each code point fallswithin the range of [0, 0xD7FF] and [0xE000, 0x10FFFF].
• Ifvalue isBYTES, each element in the array is an extended ASCIIcharacter value in the range of [0, 255].

To convert from an array of code points to aSTRING orBYTES, seeCODE_POINTS_TO_STRING orCODE_POINTS_TO_BYTES.

Return type

ARRAY<INT64>

Examples

The following examples get the code points for each element in an array ofwords.

SELECT'foo'ASword,TO_CODE_POINTS('foo')AScode_points/*---------+------------------------------------+ | word    | code_points                        | +---------+------------------------------------+ | foo     | [102, 111, 111]                    | +---------+------------------------------------*/

SELECT'bar'ASword,TO_CODE_POINTS('bar')AScode_points/*---------+------------------------------------+ | word    | code_points                        | +---------+------------------------------------+ | bar     | [98, 97, 114]                      | +---------+------------------------------------*/

SELECT'baz'ASword,TO_CODE_POINTS('baz')AScode_points/*---------+------------------------------------+ | word    | code_points                        | +---------+------------------------------------+ | baz     | [98, 97, 122]                      | +---------+------------------------------------*/

SELECT'giraffe'ASword,TO_CODE_POINTS('giraffe')AScode_points/*---------+------------------------------------+ | word    | code_points                        | +---------+------------------------------------+ | giraffe | [103, 105, 114, 97, 102, 102, 101] | +---------+------------------------------------*/

SELECT'llama'ASword,TO_CODE_POINTS('llama')AScode_points/*---------+------------------------------------+ | word    | code_points                        | +---------+------------------------------------+ | llama   | [108, 108, 97, 109, 97]            | +---------+------------------------------------*/

The following examples convert integer representations ofBYTES to theircorresponding ASCII character values.

SELECTb'\x66\x6f\x6f'ASbytes_value,TO_CODE_POINTS(b'\x66\x6f\x6f')ASbytes_value_as_integer/*------------------+------------------------+ | bytes_value      | bytes_value_as_integer | +------------------+------------------------+ | foo              | [102, 111, 111]        | +------------------+------------------------*/

SELECTb'\x00\x01\x10\xff'ASbytes_value,TO_CODE_POINTS(b'\x00\x01\x10\xff')ASbytes_value_as_integer/*------------------+------------------------+ | bytes_value      | bytes_value_as_integer | +------------------+------------------------+ | \x00\x01\x10\xff | [0, 1, 16, 255]        | +------------------+------------------------*/

The following example demonstrates the difference between aBYTES result and aSTRING result. Notice that the characterĀ is represented as a two-byteUnicode sequence. As a result, theBYTES version ofTO_CODE_POINTS returnsan array with two elements, while theSTRING version returns an array with asingle element.

SELECTTO_CODE_POINTS(b'Ā')ASb_result,TO_CODE_POINTS('Ā')ASs_result;/*------------+----------+ | b_result   | s_result | +------------+----------+ | [196, 128] | [256]    | +------------+----------*/

TO_HEX

TO_HEX(bytes)

Description

Converts a sequence ofBYTES into a hexadecimalSTRING. Converts each bytein theSTRING as two hexadecimal characters in the range(0..9, a..f). To convert a hexadecimal-encodedSTRING toBYTES, useFROM_HEX.

Return type

STRING

Example

SELECTb'\x00\x01\x02\x03\xAA\xEE\xEF\xFF'ASbyte_string,TO_HEX(b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF')AShex_string/*----------------------------------+------------------+ | byte_string                      | hex_string       | +----------------------------------+------------------+ | \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff | +----------------------------------+------------------*/

TRIM

TRIM(value_to_trim[,set_of_characters_to_remove])

Description

Takes aSTRING orBYTES value to trim.

If the value to trim is aSTRING, removes from this value all leading andtrailing Unicode code points inset_of_characters_to_remove.The set of code points is optional. If it isn't specified, allwhitespace characters are removed from the beginning and end of thevalue to trim.

If the value to trim isBYTES, removes from this value all leading andtrailing bytes inset_of_characters_to_remove. The set of bytes is required.

Return type

• STRING ifvalue_to_trim is aSTRING value.
• BYTES ifvalue_to_trim is aBYTES value.

Examples

In the following example, all leading and trailing whitespace characters areremoved fromitem becauseset_of_characters_to_remove isn't specified.

SELECTCONCAT('#',TRIM('   apple   '),'#')ASexample/*----------+ | example  | +----------+ | #apple#  | +----------*/

In the following example, all leading and trailing* characters are removedfrom 'apple'.

SELECTTRIM('***apple***','*')ASexample/*---------+ | example | +---------+ | apple   | +---------*/

In the following example, all leading and trailingx,y, andz charactersare removed from 'xzxapplexxy'.

SELECTTRIM('xzxapplexxy','xyz')asexample/*---------+ | example | +---------+ | apple   | +---------*/

In the following example, examine howTRIM interprets characters asUnicode code-points. If your trailing character set contains a combiningdiacritic mark over a particular letter,TRIM might strip thesame diacritic mark from a different letter.

SELECTTRIM('abaW̊','Y̊')ASa,TRIM('W̊aba','Y̊')ASb,TRIM('abaŪ̊','Y̊')ASc,TRIM('Ū̊aba','Y̊')ASd/*------+------+------+------+ | a    | b    | c    | d    | +------+------+------+------+ | abaW | W̊aba | abaŪ | Ūaba | +------+------+------+------*/

In the following example, all leading and trailingb'n',b'a',b'\xab'bytes are removed fromitem.

SELECTb'apple',TRIM(b'apple',b'na\xab')ASexample-- Note that the result of TRIM is of type BYTES, displayed as a base64-encoded string./*----------------------+------------------+ | item                 | example          | +----------------------+------------------+ | YXBwbGU=             | cHBsZQ==         | +----------------------+------------------*/

UCASE

UCASE(val)

Alias forUPPER.

UPPER

UPPER(value)

Description

ForSTRING arguments, returns the original string with all alphabeticcharacters in uppercase. Mapping between uppercase and lowercase is doneaccording to theUnicode Character Databasewithout taking into account language-specific mappings.

ForBYTES arguments, the argument is treated as ASCII text, with all bytesgreater than 127 left intact.

Return type

STRING orBYTES

Examples

SELECTUPPER('foo')ASexample/*---------+ | example | +---------+ | FOO     | +---------*/