String functions

GoogleSQL for BigQuery 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

Name	Summary
`ARRAY_TO_STRING`	Produces a concatenation of the elements in an array as a`STRING` value. For more information, seeArray functions.
`ASCII`	Gets the ASCII code for the first character or byte in a`STRING` or`BYTES` value.
`BYTE_LENGTH`	Gets the number of`BYTES` in a`STRING` or`BYTES` value.
`CHAR_LENGTH`	Gets the number of characters in a`STRING` value.
`CHARACTER_LENGTH`	Synonym for`CHAR_LENGTH`.
`CHR`	Converts a Unicode code point to a character.
`CODE_POINTS_TO_BYTES`	Converts an array of extended ASCII code points to a`BYTES` value.
`CODE_POINTS_TO_STRING`	Converts an array of extended ASCII code points to a`STRING` value.
`COLLATE`	Combines a`STRING` value and a collation specification into a collation specification-supported`STRING` value.
`CONCAT`	Concatenates one or more`STRING` or`BYTES` values into a single result.
`CONTAINS_SUBSTR`	Performs a normalized, case-insensitive search to see if a value exists as a substring in an expression.
`EDIT_DISTANCE`	Computes the Levenshtein distance between two`STRING` or`BYTES` values.
`ENDS_WITH`	Checks if a`STRING` or`BYTES` value is the suffix of another value.
`FORMAT`	Formats data and produces the results as a`STRING` value.
`FROM_BASE32`	Converts a base32-encoded`STRING` value into a`BYTES` value.
`FROM_BASE64`	Converts a base64-encoded`STRING` value into a`BYTES` value.
`FROM_HEX`	Converts a hexadecimal-encoded`STRING` value into a`BYTES` value.
`INITCAP`	Formats a`STRING` as proper case, which means that the first character in each word is uppercase and all other characters are lowercase.
`INSTR`	Finds the position of a subvalue inside another value, optionally starting the search at a given offset or occurrence.
`LAX_STRING`	Attempts to convert a JSON value to a SQL`STRING` value. For more information, seeJSON functions.
`LEFT`	Gets the specified leftmost portion from a`STRING` or`BYTES` value.
`LENGTH`	Gets the length of a`STRING` or`BYTES` value.
`LOWER`	Formats alphabetic characters in a`STRING` value as lowercase. Formats ASCII characters in a`BYTES` value as lowercase.
`LPAD`	Prepends a`STRING` or`BYTES` value with a pattern.
`LTRIM`	Identical to the`TRIM` function, but only removes leading characters.
`NORMALIZE`	Case-sensitively normalizes the characters in a`STRING` value.
`NORMALIZE_AND_CASEFOLD`	Case-insensitively normalizes the characters in a`STRING` value.
`OCTET_LENGTH`	Alias for`BYTE_LENGTH`.
`REGEXP_CONTAINS`	Checks if a value is a partial match for a regular expression.
`REGEXP_EXTRACT`	Produces a substring that matches a regular expression.
`REGEXP_EXTRACT_ALL`	Produces an array of all substrings that match a regular expression.
`REGEXP_INSTR`	Finds the position of a regular expression match in a value, optionally starting the search at a given offset or occurrence.
`REGEXP_REPLACE`	Produces a`STRING` value where all substrings that match a regular expression are replaced with a specified value.
`REGEXP_SUBSTR`	Synonym for`REGEXP_EXTRACT`.
`REPEAT`	Produces a`STRING` or`BYTES` value that consists of an original value, repeated.
`REPLACE`	Replaces all occurrences of a pattern with another pattern in a`STRING` or`BYTES` value.
`REVERSE`	Reverses a`STRING` or`BYTES` value.
`RIGHT`	Gets the specified rightmost portion from a`STRING` or`BYTES` value.
`RPAD`	Appends a`STRING` or`BYTES` value with a pattern.
`RTRIM`	Identical to the`TRIM` function, but only removes trailing characters.
`SAFE_CONVERT_BYTES_TO_STRING`	Converts a`BYTES` value to a`STRING` value and replace any invalid UTF-8 characters with the Unicode replacement character,`U+FFFD`.
`SOUNDEX`	Gets the Soundex codes for words in a`STRING` value.
`SPLIT`	Splits a`STRING` or`BYTES` value, using a delimiter.
`STARTS_WITH`	Checks if a`STRING` or`BYTES` value is a prefix of another value.
`STRING` (JSON)	Converts a JSON string to a SQL`STRING` value. For more information, seeJSON functions.
`STRING` (Timestamp)	Converts a`TIMESTAMP` value to a`STRING` value. For more information, seeTimestamp functions.
`STRING_AGG`	Concatenates non-`NULLSTRING` or`BYTES` values. For more information, seeAggregate functions.
`STRPOS`	Finds the position of the first occurrence of a subvalue inside another value.
`SUBSTR`	Gets a portion of a`STRING` or`BYTES` value.
`SUBSTRING`	Alias for`SUBSTR`
`TO_BASE32`	Converts a`BYTES` value to a base32-encoded`STRING` value.
`TO_BASE64`	Converts a`BYTES` value to a base64-encoded`STRING` value.
`TO_CODE_POINTS`	Converts a`STRING` or`BYTES` value into an array of extended ASCII code points.
`TO_HEX`	Converts a`BYTES` value to a hexadecimal`STRING` value.
`TRANSLATE`	Within a value, replaces each source character with the corresponding target character.
`TRIM`	Removes the specified leading and trailing Unicode code points or bytes from a`STRING` or`BYTES` value.
`UNICODE`	Gets the Unicode code point for the first character in a value.
`UPPER`	Formats alphabetic characters in a`STRING` value as uppercase. Formats ASCII characters in a`BYTES` value as uppercase.

`ASCII`

ASCII(value)

Description

Returns the ASCII code for the first character or byte invalue. Returns0 ifvalue is empty or the ASCII code is0 for the first characteror byte.

Return type

INT64

Examples

SELECTASCII('abcd')asA,ASCII('a')asB,ASCII('')asC,ASCII(NULL)asD;/*-------+-------+-------+-------* | A     | B     | C     | D     | +-------+-------+-------+-------+ | 97    | 97    | 0     | NULL  | *-------+-------+-------+-------*/

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

`CHR`

CHR(value)

Description

Takes a Unicodecode point and returnsthe character that matches the code point. Each valid code point should fallwithin the range of [0, 0xD7FF] and [0xE000, 0x10FFFF]. Returns an empty stringif the code point is0. If an invalid Unicode code point is specified, anerror is returned.

To work with an array of Unicode code points, seeCODE_POINTS_TO_STRING

Return type

STRING

Examples

SELECTCHR(65)ASA,CHR(255)ASB,CHR(513)ASC,CHR(1024)ASD;/*-------+-------+-------+-------* | A     | B     | C     | D     | +-------+-------+-------+-------+ | A     | ÿ     | ȁ     | Ѐ     | *-------+-------+-------+-------*/

SELECTCHR(97)ASA,CHR(0xF9B5)ASB,CHR(0)ASC,CHR(NULL)ASD;/*-------+-------+-------+-------* | A     | B     | C     | D     | +-------+-------+-------+-------+ | a     | 例    |       | NULL  | *-------+-------+-------+-------*/

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

`COLLATE`

COLLATE(value,collate_specification)

Takes aSTRING and acollation specification. ReturnsaSTRING with a collation specification. Ifcollate_specification is empty,returns a value with collation removed from theSTRING.

The collation specification defines how the resultingSTRING can be comparedand sorted. To learn more, seeCollation.

collation_specification must be a string literal, otherwise an error isthrown.
ReturnsNULL ifvalue isNULL.

Return type

STRING

Examples

In this example, the weight ofa is less than the weight ofZ. Thisis because the collate specification,und:ci assigns more weight toZ.

WITHWordsAS(SELECTCOLLATE('a','und:ci')ASchar1,COLLATE('Z','und:ci')ASchar2)SELECT(Words.char1 <Words.char2)ASa_less_than_ZFROMWords;/*----------------* | a_less_than_Z  | +----------------+ | TRUE           | *----------------*/

In this example, the weight ofa is greater than the weight ofZ. Thisis because the default collate specification assigns more weight toa.

WITHWordsAS(SELECT'a'ASchar1,'Z'ASchar2)SELECT(Words.char1 <Words.char2)ASa_less_than_ZFROMWords;/*----------------* | a_less_than_Z  | +----------------+ | FALSE          | *----------------*/

`CONCAT`

CONCAT(value1[,...])

Description

Concatenates one or more values into a single result. All values must beBYTES or data types that can be cast toSTRING.

The function returnsNULL if any input argument isNULL.

Note: You can also use the || concatenation operator to concatenatevalues into a string.

Return type

STRING orBYTES

Examples

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

SELECTCONCAT('Summer',' ',1923)asrelease_date;/*---------------------* | release_date        | +---------------------+ | Summer 1923         | *---------------------*/

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

`CONTAINS_SUBSTR`

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

Description

Performs a normalized, case-insensitive search to see if a value exists as asubstring in an expression. ReturnsTRUE if the value exists, otherwisereturnsFALSE.

Before values are compared, they arenormalized and case folded withNFKCnormalization. Wildcard searches aren'tsupported.

Arguments

search_value_literal: The value to search for. It must be aSTRINGliteral or aSTRING constant expression.
expression: The data to search over. The expression can be a column ortable reference. A table reference is evaluated as aSTRUCT whose fieldsare the columns of the table. A column reference is evaluated as one thefollowing data types:
- STRING
- INT64
- BOOL
- NUMERIC
- BIGNUMERIC
- TIMESTAMP
- TIME
- DATE
- DATETIME
- ARRAY
- STRUCT
When the expression is evaluated, the result is cast to aSTRING, and thenthe function looks for the search value in the result.
You can perform a cross-field search on an expression that evaluates to aSTRUCT orARRAY. If the expression evaluates to aSTRUCT, thecross-field search is recursive and includes all subfields inside theSTRUCT.
In a cross-field search, each field and subfield is individually convertedto a string and searched for the value. The function returnsTRUE if atleast one field includes the search value; otherwise, if at least one fieldisNULL, it returnsNULL; otherwise, if the search value isn't foundand all fields are non-NULL, it returnsFALSE.
If the expression isNULL, the return value isNULL.
json_scope: A named argument with aSTRING value.Takes one of the following values to indicate the scope ofJSON data to besearched. It has no effect ifexpression isn'tJSON or doesn'tcontain aJSON field.
- 'JSON_VALUES': Only theJSON values are searched. Ifjson_scope isnot provided, this is used by default.
- 'JSON_KEYS': Only theJSON keys are searched.
- 'JSON_KEYS_AND_VALUES': TheJSON keys and values are searched.

Return type

BOOL

Examples

The following query returnsTRUE because this case-insensitive matchwas found:blue house andBlue house.

SELECTCONTAINS_SUBSTR('the blue house','Blue house')ASresult;/*--------* | result | +--------+ | true   | *--------*/

The following query returnsTRUE similar to the above example, but in thiscase the search value is a constant expression with CONCAT function.

SELECTCONTAINS_SUBSTR('the blue house',CONCAT('Blue ','house'))ASresult;/*--------* | result | +--------+ | true   | *--------*/

The following query returnsFALSE becauseblue wasn't foundinthe red house.

SELECTCONTAINS_SUBSTR('the red house','blue')ASresult;/*--------* | result | +--------+ | false  | *--------*/

The following query returnsTRUE becauseⅨ andIX represent the samenormalized value.

SELECT'\u2168 day'ASa,'IX'ASb,CONTAINS_SUBSTR('\u2168','IX')ASresult;/*----------------------* | a      | b  | result | +----------------------+ | Ⅸ day | IX | true   | *----------------------*/

The following query returnsTRUE because35 was found inside aSTRUCT field.

SELECTCONTAINS_SUBSTR((23,35,41),'35')ASresult;/*--------* | result | +--------+ | true   | *--------*/

The following query returnsTRUE becausejk was found during arecursive search inside aSTRUCT.

SELECTCONTAINS_SUBSTR(('abc',['def','ghi','jkl'],'mno'),'jk');/*--------* | result | +--------+ | true   | *--------*/

The following query returnsTRUE becauseNULLs are ignored whena match is found found inside aSTRUCT orARRAY.

SELECTCONTAINS_SUBSTR((23,NULL,41),'41')ASresult;/*--------* | result | +--------+ | true   | *--------*/

The following query returnsNULL because aNULL existed in aSTRUCT thatdidn't result in a match.

SELECTCONTAINS_SUBSTR((23,NULL,41),'35')ASresult;/*--------* | result | +--------+ | null   | *--------*/

In the following query, an error is thrown because the search value can't bea literalNULL.

SELECTCONTAINS_SUBSTR('hello',NULL)ASresult;-- Throws an error

The following examples reference a table calledRecipes that you can emulatewith aWITH clause like this:

WITHRecipesAS(SELECT'Blueberry pancakes'asBreakfast,'Egg salad sandwich'asLunch,'Potato dumplings'asDinnerUNIONALLSELECT'Potato pancakes','Toasted cheese sandwich','Beef stroganoff'UNIONALLSELECT'Ham scramble','Steak avocado salad','Tomato pasta'UNIONALLSELECT'Avocado toast','Tomato soup','Blueberry salmon'UNIONALLSELECT'Corned beef hash','Lentil potato soup','Glazed ham')SELECT*FROMRecipes;/*-------------------+-------------------------+------------------* | 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       | *-------------------+-------------------------+------------------*/

The following query searches across all columns of theRecipes table for thevaluetoast and returns the rows that contain this value.

SELECT*FROMRecipesWHERECONTAINS_SUBSTR(Recipes,'toast');/*-------------------+-------------------------+------------------* | Breakfast         | Lunch                   | Dinner           | +-------------------+-------------------------+------------------+ | Potato pancakes   | Toasted cheese sandwich | Beef stroganoff  | | Avocado toast     | Tomato soup             | Blueberry samon  | *-------------------+-------------------------+------------------*/

The following query searches theLunch andDinner columns of theRecipe table for the valuepotato and returns the row if either columncontains this value.

SELECT*FROMRecipesWHERECONTAINS_SUBSTR((Lunch,Dinner),'potato');/*-------------------+-------------------------+------------------* | Breakfast         | Lunch                   | Dinner           | +-------------------+-------------------------+------------------+ | Bluberry pancakes | Egg salad sandwich      | Potato dumplings | | Corned beef hash  | Lentil potato soup      | Glazed ham       | *-------------------+-------------------------+------------------*/

The following query searches across all columns of theRecipes tableexcept for theLunch andDinner columns. It returns the rows of anycolumns other thanLunch orDinner that contain the valuepotato.

SELECT*FROMRecipesWHERECONTAINS_SUBSTR((SELECTASSTRUCTRecipes.*EXCEPT(Lunch,Dinner)),'potato');/*-------------------+-------------------------+------------------* | Breakfast         | Lunch                   | Dinner           | +-------------------+-------------------------+------------------+ | Potato pancakes   | Toasted cheese sandwich | Beef stroganoff  | *-------------------+-------------------------+------------------*/

The following query searches for the valuelunch in the JSON{"lunch":"soup"} and returnsFALSE because the defaultjson_scope is"JSON_VALUES", andlunch is aJSON key, not aJSON value.

SELECTCONTAINS_SUBSTR(JSON'{"lunch":"soup"}',"lunch")ASresult;/*--------* | result | +--------+ | FALSE  | *--------*/

The following query searches for the valuelunch in the values of the JSON{"lunch":"soup"} and returnsFALSE becauselunch is aJSON key, not aJSON value.

SELECTCONTAINS_SUBSTR(JSON'{"lunch":"soup"}',"lunch",json_scope=>"JSON_VALUES")ASresult;/*--------* | result | +--------+ | FALSE  | *--------*/

The following query searches for the valuelunch in the keys and values of theJSON{"lunch":"soup"} and returnsTRUE becauselunch is aJSON key.

SELECTCONTAINS_SUBSTR(JSON'{"lunch":"soup"}',"lunch",json_scope=>"JSON_KEYS_AND_VALUES")ASresult;/*--------* | result | +--------+ | TRUE   | *--------*/

The following query searches for the valuelunch in the keys of the JSON{"lunch":"soup"} and returnsTRUE becauselunch is aJSON key.

SELECTCONTAINS_SUBSTR(JSON'{"lunch":"soup"}',"lunch",json_scope=>"JSON_KEYS")ASresult;/*--------* | result | +--------+ | TRUE   | *--------*/

`EDIT_DISTANCE`

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

Description

Computes theLevenshtein distance between twoSTRING orBYTES values.

Definitions

value1: The firstSTRING orBYTES value to compare.
value2: The secondSTRING orBYTES value to compare.
max_distance: A named argument with aINT64 value that's greater thanor equal to zero. Represents the maximum distance between the two valuesto compute.
If this distance is exceeded, the function returns this value.The default value for this argument is the maximum size ofvalue1 andvalue2.

Details

Ifvalue1 orvalue2 isNULL,NULL is returned.

You can only compare values of the same type. Otherwise, an error is produced.

Return type

INT64

Examples

In the following example, the first character in both strings is different:

SELECTEDIT_DISTANCE('a','b')ASresults;/*---------* | results | +---------+ | 1       | *---------*/

In the following example, the first and second characters in both strings aredifferent:

SELECTEDIT_DISTANCE('aa','b')ASresults;/*---------* | results | +---------+ | 2       | *---------*/

In the following example, only the first character in both strings isdifferent:

SELECTEDIT_DISTANCE('aa','ba')ASresults;/*---------* | results | +---------+ | 1       | *---------*/

In the following example, the last six characters are different, but becausethe maximum distance is2, this function exits early and returns2, themaximum distance:

SELECTEDIT_DISTANCE('abcdefg','a',max_distance=>2)ASresults;/*---------* | results | +---------+ | 2       | *---------*/

`ENDS_WITH`

ENDS_WITH(value,suffix)

Description

Takes twoSTRING orBYTES values. ReturnsTRUE ifsuffixis a suffix ofvalue.

This function supports specifyingcollation.

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

Description	Statement	Result
Simple integer	FORMAT('%d', 10)	10
Integer with left blank padding	FORMAT('\|%10d\|', 11)	\| 11\|
Integer with left zero padding	FORMAT('+%010d+', 12)	+0000000012+
Integer with commas	FORMAT("%'d", 123456789)	123,456,789
STRING	FORMAT('-%s-', 'abcd efg')	-abcd efg-
FLOAT64	FORMAT('%f %E', 1.1, 2.2)	1.100000 2.200000E+00
DATE	FORMAT('%t', date '2015-09-01')	2015-09-01
TIMESTAMP	FORMAT('%t', timestamp '2015-09-01 12:34:56America/Los_Angeles')	2015‑09‑01 19:34:56+00

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:

Additional information about format specifiers:

Format specifiers

Specifier	Description	Examples	Types
`d` or`i`	Decimal integer	392	`INT64`
`o`	Octal Note: If an`INT64` value is negative, an error is produced.	610	`INT64`
`x`	Hexadecimal integer Note: If an`INT64` value is negative, an error is produced.	7fa	`INT64`
`X`	Hexadecimal integer (uppercase) Note: If an`INT64` value is negative, an error is produced.	7FA	`INT64`
`f`	Decimal notation, in [-](integer part).(fractional part) for finite values, and in lowercase for non-finite values	392.650000 inf nan	`NUMERIC` `BIGNUMERIC` `FLOAT64`
`F`	Decimal notation, in [-](integer part).(fractional part) for finite values, and in uppercase for non-finite values	392.650000 INF NAN	`NUMERIC` `BIGNUMERIC` `FLOAT64`
`e`	Scientific notation (mantissa/exponent), lowercase	3.926500e+02 inf nan	`NUMERIC` `BIGNUMERIC` `FLOAT64`
`E`	Scientific notation (mantissa/exponent), uppercase	3.926500E+02 INF NAN	`NUMERIC` `BIGNUMERIC` `FLOAT64`
`g`	Either decimal notation or scientific notation, depending on the input value's exponent and the specified precision. Lowercase. See%g and %G behavior for details.	392.65 3.9265e+07 inf nan	`NUMERIC` `BIGNUMERIC` `FLOAT64`
`G`	Either decimal notation or scientific notation, depending on the input value's exponent and the specified precision. Uppercase. See%g and %G behavior for details.	392.65 3.9265E+07 INF NAN	`NUMERIC` `BIGNUMERIC` `FLOAT64`
`p`	Produces a one-line printable string representing JSON. See%p and %P behavior.	{"month":10,"year":2019}	`JSON`
`P`	Produces a multi-line printable string representing JSON. See%p and %P behavior.	{ "month": 10, "year": 2019}	`JSON`
`s`	String of characters	sample	`STRING`
`t`	Returns a printable string representing the value. Often looks similar to casting the argument to`STRING`. See%t and %T behavior.	sample 2014‑01‑01	Any type
`T`	Produces a string that's a valid GoogleSQL constant with a similar type to the value's type (maybe wider, or maybe string). See%t and %T behavior.	'sample' b'bytes sample' 1234 2.3 date '2014‑01‑01'	Any type
`%`	'%%' produces a single '%'	%	n/a

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	Description
`-`	Left-justify within the given field width; Right justification is thedefault (see width sub-specifier)
`+`	Forces to precede the result with a plus or minus sign (`+`or`-`) even for positive numbers. By default, only negative numbersare preceded with a`-` sign
<space>	If no sign is going to be written, a blank space is inserted before thevalue
`#`	For `%o`, `%x`, and `%X`, this flag means to precede the value with 0, 0x or 0X respectively for values different than zero. For `%f`, `%F`, `%e`, and `%E`, this flag means to add the decimal point even when there is no fractional part, unless the value is non-finite. For `%g` and `%G`, this flag means to add the decimal point even when there is no fractional part unless the value is non-finite, and never remove the trailing zeros after the decimal point.
`0`	Left-pads the number with zeroes (0) instead of spaces when padding is specified (see width sub-specifier)
`'`	Formats integers using the appropriating grouping character. For example: `FORMAT("%'d", 12345678)` returns`12,345,678` `FORMAT("%'x", 12345678)` returns`bc:614e` `FORMAT("%'o", 55555)` returns`15,4403` This flag is only relevant for decimal, hex, and octal values.

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

Width	Description
<number>	Minimum number of characters to be printed. If the value to be printed is shorter than this number, the result is padded with blank spaces. The value isn't truncated even if the result is larger
`*`	The width isn't specified in the format string, but as an additional integer value argument preceding the argument that has to be formatted

Precision

Precision	Description
`.`<number>	For integer specifiers `%d`, `%i`, `%o`, `%u`, `%x`, and `%X`: precision specifies the minimum number of digits to be written. If the value to be written is shorter than this number, the result is padded with trailing zeros. The value isn't truncated even if the result is longer. A precision of 0 means that no character is written for the value 0. For specifiers `%a`, `%A`, `%e`, `%E`, `%f`, and `%F`: this is the number of digits to be printed after the decimal point. The default value is 6. For specifiers `%g` and `%G`: this is the number of significant digits to be printed, before the removal of the trailing zeros after the decimal point. The default value is 6.
`.*`	The precision isn't specified in the format string, but as an additional integer value argument preceding the argument that has to be formatted

%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:

Type

JSON

JSON input:

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

Produces a one-line printable string representing JSON:

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

JSON input:

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

Produces a multi-line printable string representing JSON:

{  "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:

Type	%t	%T
`NULL` of any type	NULL	NULL
`INT64`	123	123
NUMERIC	123.0(always with .0)	NUMERIC "123.0"
FLOAT64	123.0(always with .0) 123e+10 `inf` `-inf` `NaN`	123.0(always with .0) 123e+10 CAST("inf" AS <type>) CAST("-inf" AS <type>) CAST("nan" AS <type>)
STRING	unquoted string value	quoted string literal
BYTES	unquoted escaped bytes e.g., abc\x01\x02	quoted bytes literal e.g., b"abc\x01\x02"
BOOL	boolean value	boolean value
DATE	2011-02-03	DATE "2011-02-03"
TIMESTAMP	2011-02-03 04:05:06+00	TIMESTAMP "2011-02-03 04:05:06+00"
INTERVAL	1-2 3 4:5:6.789	INTERVAL "1-2 3 4:5:6.789" YEAR TO SECOND
ARRAY	[value, value, ...] where values are formatted with %t	[value, value, ...] where values are formatted with %T
STRUCT	(value, value, ...) where fields are formatted with %t	(value, value, ...) where fields are formatted with %T Special cases: Zero fields: STRUCT() One field: STRUCT(value)
JSON	one-line printable string representing JSON. {"name":"apple","stock":3}	one-line printable string representing a JSON literal. 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 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     | *------------------+--------------*/

`INITCAP`

INITCAP(value[,delimiters])

Description

Takes aSTRING and returns it with the first character in each word inuppercase and all other characters in lowercase. Non-alphabetic charactersremain the same.

delimiters is an optional string argument that's used to override the defaultset of characters used to separate words. Ifdelimiters isn't specified, itdefaults to the following characters:
<whitespace> [ ] ( ) { } / | \ < > ! ? @ " ^ # $ & ~ _ , . : ; * % + -

Ifvalue ordelimiters isNULL, the function returnsNULL.

Return type

STRING

Examples

SELECT'Hello World-everyone!'ASvalue,INITCAP('Hello World-everyone!')ASinitcap_value/*-------------------------------+-------------------------------* | value                         | initcap_value                 | +-------------------------------+-------------------------------+ | Hello World-everyone!         | Hello World-Everyone!         | *-------------------------------+-------------------------------*/

SELECT'Apples1oranges2pears'asvalue,'12'ASdelimiters,INITCAP('Apples1oranges2pears','12')ASinitcap_value/*----------------------+------------+----------------------* | value                | delimiters | initcap_value        | +----------------------+------------+----------------------+ | Apples1oranges2pears | 12         | Apples1Oranges2Pears | *----------------------+------------+----------------------*/

`INSTR`

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

Description

Returns the lowest 1-based position ofsubvalue invalue.value andsubvalue must be the same type, eitherSTRING orBYTES.

Ifposition is specified, the search starts at this position invalue, otherwise it starts at1, which is the beginning ofvalue. Ifposition is negative, the function searches backwardsfrom the end ofvalue, with-1 indicating the last character.position is of typeINT64 and can't be0.

Ifoccurrence is specified, the search returns the position of a specificinstance ofsubvalue invalue. If not specified,occurrencedefaults to1 and returns the position of the first occurrence.Foroccurrence >1, the function includes overlapping occurrences.occurrence is of typeINT64 and must be positive.

This function supports specifyingcollation.

Returns0 if:

No match is found.
Ifoccurrence is greater than the number of matches found.
Ifposition is greater than the length ofvalue.

ReturnsNULL if:

Any input argument isNULL.

Returns an error if:

position is0.
occurrence is0 or negative.

Return type

INT64

Examples

SELECT'banana'ASvalue,'an'ASsubvalue,1ASposition,1ASoccurrence,INSTR('banana','an',1,1)ASinstr;/*--------------+--------------+----------+------------+-------* | value        | subvalue     | position | occurrence | instr | +--------------+--------------+----------+------------+-------+ | banana       | an           | 1        | 1          | 2     | *--------------+--------------+----------+------------+-------*/

SELECT'banana'ASvalue,'an'ASsubvalue,1ASposition,2ASoccurrence,INSTR('banana','an',1,2)ASinstr;/*--------------+--------------+----------+------------+-------* | value        | subvalue     | position | occurrence | instr | +--------------+--------------+----------+------------+-------+ | banana       | an           | 1        | 2          | 4     | *--------------+--------------+----------+------------+-------*/

SELECT'banana'ASvalue,'an'ASsubvalue,1ASposition,3ASoccurrence,INSTR('banana','an',1,3)ASinstr;/*--------------+--------------+----------+------------+-------* | value        | subvalue     | position | occurrence | instr | +--------------+--------------+----------+------------+-------+ | banana       | an           | 1        | 3          | 0     | *--------------+--------------+----------+------------+-------*/

SELECT'banana'ASvalue,'an'ASsubvalue,3ASposition,1ASoccurrence,INSTR('banana','an',3,1)ASinstr;/*--------------+--------------+----------+------------+-------* | value        | subvalue     | position | occurrence | instr | +--------------+--------------+----------+------------+-------+ | banana       | an           | 3        | 1          | 4     | *--------------+--------------+----------+------------+-------*/

SELECT'banana'ASvalue,'an'ASsubvalue,-1ASposition,1ASoccurrence,INSTR('banana','an',-1,1)ASinstr;/*--------------+--------------+----------+------------+-------* | value        | subvalue     | position | occurrence | instr | +--------------+--------------+----------+------------+-------+ | banana       | an           | -1       | 1          | 4     | *--------------+--------------+----------+------------+-------*/

SELECT'banana'ASvalue,'an'ASsubvalue,-3ASposition,1ASoccurrence,INSTR('banana','an',-3,1)ASinstr;/*--------------+--------------+----------+------------+-------* | value        | subvalue     | position | occurrence | instr | +--------------+--------------+----------+------------+-------+ | banana       | an           | -3       | 1          | 4     | *--------------+--------------+----------+------------+-------*/

SELECT'banana'ASvalue,'ann'ASsubvalue,1ASposition,1ASoccurrence,INSTR('banana','ann',1,1)ASinstr;/*--------------+--------------+----------+------------+-------* | value        | subvalue     | position | occurrence | instr | +--------------+--------------+----------+------------+-------+ | banana       | ann          | 1        | 1          | 0     | *--------------+--------------+----------+------------+-------*/

SELECT'helloooo'ASvalue,'oo'ASsubvalue,1ASposition,1ASoccurrence,INSTR('helloooo','oo',1,1)ASinstr;/*--------------+--------------+----------+------------+-------* | value        | subvalue     | position | occurrence | instr | +--------------+--------------+----------+------------+-------+ | helloooo     | oo           | 1        | 1          | 5     | *--------------+--------------+----------+------------+-------*/

SELECT'helloooo'ASvalue,'oo'ASsubvalue,1ASposition,2ASoccurrence,INSTR('helloooo','oo',1,2)ASinstr;/*--------------+--------------+----------+------------+-------* | value        | subvalue     | position | occurrence | instr | +--------------+--------------+----------+------------+-------+ | helloooo     | oo           | 1        | 2          | 6     | *--------------+--------------+----------+------------+-------*/

`LEFT`

LEFT(value,length)

Description

Returns aSTRING orBYTES value that consists of the specifiednumber of leftmost characters or bytes fromvalue. Thelength is anINT64 that specifies the length of the returnedvalue. Ifvalue is of typeBYTES,length is the number of leftmost bytesto return. Ifvalue isSTRING,length is the number of leftmost charactersto return.

Iflength is 0, an emptySTRING orBYTES value will bereturned. Iflength is negative, an error will be returned. Iflengthexceeds the number of characters or bytes fromvalue, the originalvaluewill be returned.

Return type

STRING orBYTES

Examples

SELECTLEFT('banana',3)ASresults/*---------* | results |  +--------+ | ban     | *---------*/

SELECTLEFT(b'\xab\xcd\xef\xaa\xbb',3)ASresults-- Note that the result of LEFT is of type BYTES, displayed as a base64-encoded string./*---------* | results | +---------+ | q83v    | *---------*/

`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:

Value	Name	Description
`NFC`	Normalization Form Canonical Composition	Decomposes and recomposes characters by canonical equivalence.
`NFKC`	Normalization Form Compatibility Composition	Decomposes characters by compatibility, then recomposes them by canonical equivalence.
`NFD`	Normalization Form Canonical Decomposition	Decomposes characters by canonical equivalence, and multiple combining characters are arranged in a specific order.
`NFKD`	Normalization Form Compatibility Decomposition	Decomposes characters by compatibility, and multiple combining characters are arranged in a specific order.

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:

Value	Name	Description
`NFC`	Normalization Form Canonical Composition	Decomposes and recomposes characters by canonical equivalence.
`NFKC`	Normalization Form Compatibility Composition	Decomposes characters by compatibility, then recomposes them by canonical equivalence.
`NFD`	Normalization Form Canonical Decomposition	Decomposes characters by canonical equivalence, and multiple combining characters are arranged in a specific order.
`NFKD`	Normalization Form Compatibility Decomposition	Decomposes characters by compatibility, and multiple combining characters are arranged in a specific order.

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)ASnkfd,NORMALIZE_AND_CASEFOLD('\u2168',NFKC)=NORMALIZE_AND_CASEFOLD('IX',NFKC)ASnkfc;/*---+----+-------+-------+------+------* | a | b  | nfd   | nfc   | nkfd | nkfc | +---+----+-------+-------+------+------+ | Ⅸ | 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 | +---+----+-------+-------+------+------+ | Å | Å  | 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$.

Note: GoogleSQL provides regular expression support using the re2 library; see that documentation for itsregular expression syntax.

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[,position[,occurrence]])

Description

Returns the 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.

Ifposition is specified, the search starts at thisposition invalue, otherwise it starts at the beginning ofvalue. Theposition must be a positive integer and can't be 0. Ifposition is greaterthan the length ofvalue,NULL is returned.

Ifoccurrence is specified, the search returns a specific occurrence of theregexp invalue, otherwise returns the first match. Ifoccurrence isgreater than the number of matches found,NULL is returned. Foroccurrence > 1, the function searches for additional occurrences beginningwith the character following the previous occurrence.

Returns an error if:

The regular expression is invalid
The regular expression has more than one capturing group
Theposition isn't a positive integer
Theoccurrence isn't a positive integer

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

WITHexampleAS(SELECT'Hello Helloo and Hellooo'ASvalue,'H?ello+'ASregex,1asposition,1ASoccurrenceUNIONALLSELECT'Hello Helloo and Hellooo','H?ello+',1,2UNIONALLSELECT'Hello Helloo and Hellooo','H?ello+',1,3UNIONALLSELECT'Hello Helloo and Hellooo','H?ello+',1,4UNIONALLSELECT'Hello Helloo and Hellooo','H?ello+',2,1UNIONALLSELECT'Hello Helloo and Hellooo','H?ello+',3,1UNIONALLSELECT'Hello Helloo and Hellooo','H?ello+',3,2UNIONALLSELECT'Hello Helloo and Hellooo','H?ello+',3,3UNIONALLSELECT'Hello Helloo and Hellooo','H?ello+',20,1UNIONALLSELECT'cats&dogs&rabbits','\\w+&',1,2UNIONALLSELECT'cats&dogs&rabbits','\\w+&',2,3)SELECTvalue,regex,position,occurrence,REGEXP_EXTRACT(value,regex,position,occurrence)ASregexp_valueFROMexample;/*--------------------------+---------+----------+------------+--------------* | 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`

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_INSTR`

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

Description

Returns the lowest 1-based position of a regular expression,regexp, insource_value.source_value andregexp must be the same type, eitherSTRING orBYTES.

Ifposition is specified, the search starts at this position insource_value, otherwise it starts at1, which is the beginning ofsource_value.position is of typeINT64 and must be positive.

Ifoccurrence is specified, the search returns the position of a specificinstance ofregexp insource_value. If not specified,occurrence defaultsto1 and returns the position of the first occurrence. Foroccurrence > 1,the function searches for the next, non-overlapping occurrence.occurrence is of typeINT64 and must be positive.

You can optionally useoccurrence_position to specify where a positionin relation to anoccurrence starts. Your choices are:

0: Returns the start position ofoccurrence.
1: Returns the end position ofoccurrence +1. If theend of the occurrence is at the end ofsource_value,LENGTH(source_value) + 1 is returned.

Returns0 if:

No match is found.
Ifoccurrence is greater than the number of matches found.
Ifposition is greater than the length ofsource_value.
The regular expression is empty.

ReturnsNULL if:

position isNULL.
occurrence isNULL.

Returns an error if:

position is0 or negative.
occurrence is0 or negative.
occurrence_position is neither0 nor1.
The regular expression is invalid.
The regular expression has more than one capturing group.

Return type

INT64

Examples

SELECTREGEXP_INSTR('ab@cd-ef','@[^-]*')ASinstr_a,REGEXP_INSTR('ab@d-ef','@[^-]*')ASinstr_b,REGEXP_INSTR('abc@cd-ef','@[^-]*')ASinstr_c,REGEXP_INSTR('abc-ef','@[^-]*')ASinstr_d,/*---------------------------------------* | instr_a | instr_b | instr_c | instr_d | +---------------------------------------+ | 3       | 3       | 4       | 0       | *---------------------------------------*/

SELECTREGEXP_INSTR('a@cd-ef b@cd-ef','@[^-]*',1)ASinstr_a,REGEXP_INSTR('a@cd-ef b@cd-ef','@[^-]*',2)ASinstr_b,REGEXP_INSTR('a@cd-ef b@cd-ef','@[^-]*',3)ASinstr_c,REGEXP_INSTR('a@cd-ef b@cd-ef','@[^-]*',4)ASinstr_d,/*---------------------------------------* | instr_a | instr_b | instr_c | instr_d | +---------------------------------------+ | 2       | 2       | 10      | 10      | *---------------------------------------*/

SELECTREGEXP_INSTR('a@cd-ef b@cd-ef c@cd-ef','@[^-]*',1,1)ASinstr_a,REGEXP_INSTR('a@cd-ef b@cd-ef c@cd-ef','@[^-]*',1,2)ASinstr_b,REGEXP_INSTR('a@cd-ef b@cd-ef c@cd-ef','@[^-]*',1,3)ASinstr_c/*-----------------------------* | instr_a | instr_b | instr_c | +-----------------------------+ | 2       | 10      | 18      | *-----------------------------*/

SELECTREGEXP_INSTR('a@cd-ef','@[^-]*',1,1,0)ASinstr_a,REGEXP_INSTR('a@cd-ef','@[^-]*',1,1,1)ASinstr_b/*-------------------* | instr_a | instr_b | +-------------------+ | 2       | 5       | *-------------------*/

`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.

Note: GoogleSQL provides regular expression support using the re2 library; see that documentation for itsregular expression syntax.

Return type

STRING orBYTES

Examples

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

`REGEXP_SUBSTR`

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

Description

Synonym forREGEXP_EXTRACT.

Return type

STRING orBYTES

Examples

WITHexampleAS(SELECT'Hello World Helloo'ASvalue,'H?ello+'ASregex,1ASposition,1ASoccurrence)SELECTvalue,regex,position,occurrence,REGEXP_SUBSTR(value,regex,position,occurrence)ASregexp_valueFROMexample;/*--------------------+---------+----------+------------+--------------* | value              | regex   | position | occurrence | regexp_value | +--------------------+---------+----------+------------+--------------+ | Hello World Helloo | H?ello+ | 1        | 1          | Hello        | *--------------------+---------+----------+------------+--------------*/

`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.

This function supports specifyingcollation.

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

`RIGHT`

RIGHT(value,length)

Description

Returns aSTRING orBYTES value that consists of the specifiednumber of rightmost characters or bytes fromvalue. Thelength is anINT64 that specifies the length of the returnedvalue. Ifvalue isBYTES,length is the number of rightmost bytes toreturn. Ifvalue isSTRING,length is the number of rightmost charactersto return.

Return type

STRING orBYTES

Examples

SELECT'apple'ASexample,RIGHT('apple',3)ASright_example/*---------+---------------* | example | right_example | +---------+---------------+ | apple   | ple           | *---------+---------------*/

SELECTb'apple'ASexample,RIGHT(b'apple',3)ASright_example-- Note that the result of RIGHT is of type BYTES, displayed as a base64-encoded string./*----------+---------------* | example  | right_example | +----------+---------------+ | YXBwbGU= | cGxl          | *----------+---------------*/

`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.

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.

This function supports specifyingcollation.

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

`STARTS_WITH`

STARTS_WITH(value,prefix)

Description

Takes twoSTRING orBYTES values. ReturnsTRUE ifprefix is aprefix ofvalue.

This function supports specifyingcollation.

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.

This function supports specifyingcollation.

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.

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

`TRANSLATE`

TRANSLATE(expression,source_characters,target_characters)

Description

Inexpression, replaces each character insource_characters with thecorresponding character intarget_characters. All inputs must be the sametype, eitherSTRING orBYTES.

Each character inexpression is translated at most once.
A character inexpression that isn't present insource_characters is leftunchanged inexpression.
A character insource_characters without a corresponding character intarget_characters is omitted from the result.
A duplicate character insource_characters results in an error.

Return type

STRING orBYTES

Examples

SELECTTRANSLATE('This is a cookie','sco','zku')AStranslate/*------------------* | translate        | +------------------+ | Thiz iz a kuukie | *------------------*/

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

`UNICODE`

UNICODE(value)

Description

Returns the Unicodecode point for the first character invalue. Returns0 ifvalue is empty, or if the resulting Unicode codepoint is0.

Return type

INT64

Examples

SELECTUNICODE('âbcd')asA,UNICODE('â')asB,UNICODE('')asC,UNICODE(NULL)asD;/*-------+-------+-------+-------* | A     | B     | C     | D     | +-------+-------+-------+-------+ | 226   | 226   | 0     | NULL  | *-------+-------+-------+-------*/

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

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-10-02 UTC.

Movatterモバイル変換

String functions Stay organized with collections Save and categorize content based on your preferences.

Function list

ASCII

BYTE_LENGTH

CHAR_LENGTH

CHARACTER_LENGTH

CHR

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_STRING

COLLATE

CONCAT

CONTAINS_SUBSTR

EDIT_DISTANCE

ENDS_WITH

FORMAT

Supported format specifiers

Format specifiers

Flags

Width

Precision

%g and %G behavior

%p and %P behavior

%t and %T behavior

Error conditions

NULL argument handling

Additional semantic rules

FROM_BASE32

FROM_BASE64

FROM_HEX

INITCAP

INSTR

LEFT

LENGTH

LOWER

LPAD

LTRIM

NORMALIZE

NORMALIZE_AND_CASEFOLD

OCTET_LENGTH

REGEXP_CONTAINS

REGEXP_EXTRACT

REGEXP_EXTRACT_ALL

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_SUBSTR

REPEAT

REPLACE

REVERSE

RIGHT

RPAD

RTRIM

SAFE_CONVERT_BYTES_TO_STRING

SOUNDEX

SPLIT

STARTS_WITH

STRPOS

SUBSTR

SUBSTRING

TO_BASE32

TO_BASE64

TO_CODE_POINTS

TO_HEX

TRANSLATE

TRIM

UNICODE

UPPER

String functions

`ASCII`

`BYTE_LENGTH`

`CHAR_LENGTH`

`CHARACTER_LENGTH`

`CHR`

`CODE_POINTS_TO_BYTES`

`CODE_POINTS_TO_STRING`

`COLLATE`

`CONCAT`

`CONTAINS_SUBSTR`

`EDIT_DISTANCE`

`ENDS_WITH`

`FORMAT`

`FROM_BASE32`

`FROM_BASE64`

`FROM_HEX`

`INITCAP`

`INSTR`

`LEFT`

`LENGTH`

`LOWER`

`LPAD`

`LTRIM`

`NORMALIZE`

`NORMALIZE_AND_CASEFOLD`

`OCTET_LENGTH`

`REGEXP_CONTAINS`

`REGEXP_EXTRACT`

`REGEXP_EXTRACT_ALL`

`REGEXP_INSTR`

`REGEXP_REPLACE`

`REGEXP_SUBSTR`

`REPEAT`

`REPLACE`

`REVERSE`

`RIGHT`

`RPAD`

`RTRIM`

`SAFE_CONVERT_BYTES_TO_STRING`

`SOUNDEX`

`SPLIT`

`STARTS_WITH`

`STRPOS`

`SUBSTR`

`SUBSTRING`

`TO_BASE32`

`TO_BASE64`

`TO_CODE_POINTS`

`TO_HEX`

`TRANSLATE`

`TRIM`

`UNICODE`

`UPPER`