GoogleSQL for BigQuery supports operators.Operators are represented by special characters or keywords; they don't usefunction call syntax. An operator manipulates any number of data inputs, alsocalled operands, and returns a result.

Common conventions:

• Unless otherwise specified, all operators returnNULL when one of theoperands isNULL.
• All operators will throw an error if the computation result overflows.
• For all floating point operations,+/-inf andNaN may only be returnedif one of the operands is+/-inf orNaN. In other cases, an error isreturned.

Operator precedence

The following table lists all GoogleSQL operators from highest tolowest precedence, i.e., the order in which they will be evaluated within astatement.

For example, the logical expression:

x OR y AND z

is interpreted as:

( x OR ( y AND z ) )

Operators with the same precedence are left associative. This means that thoseoperators are grouped together starting from the left and moving right. Forexample, the expression:

x AND y AND z

is interpreted as:

( ( x AND y ) AND z )

The expression:

x * y / z

is interpreted as:

( ( x * y ) / z )

All comparison operators have the same priority, but comparison operatorsaren't associative. Therefore, parentheses are required to resolveambiguity. For example:

(x < y) IS FALSE

Operator list

Field access operator

expression.fieldname[....]

Description

Gets the value of a field. Alternatively known as the dot operator. Can beused to access nested fields. For example,expression.fieldname1.fieldname2.

Input values:

• STRUCT
• JSON

Return type

• ForSTRUCT: SQL data type offieldname. If a field isn't found inthe struct, an error is thrown.
• ForJSON:JSON. If a field isn't found in a JSON value, a SQLNULL isreturned.

Example

In the following example, the field access operations are.address and.country.

SELECTSTRUCT(STRUCT('Yonge Street'ASstreet,'Canada'AScountry)ASaddress).address.country/*---------+ | country | +---------+ | Canada  | +---------*/

Array subscript operator

array_expression"["array_subscript_specifier"]"array_subscript_specifier:{index|position_keyword(index)}position_keyword:{OFFSET|SAFE_OFFSET|ORDINAL|SAFE_ORDINAL}

SELECT["coffee","tea","milk"]ASitem_array,["coffee","tea","milk"][0]ASitem_index,["coffee","tea","milk"][OFFSET(0)]ASitem_offset,["coffee","tea","milk"][ORDINAL(1)]ASitem_ordinal,["coffee","tea","milk"][SAFE_OFFSET(6)]ASitem_safe_offset/*---------------------+------------+-------------+--------------+------------------+ | item_array          | item_index | item_offset | item_ordinal | item_safe_offset | +---------------------+------------+-------------+--------------+------------------+ | [coffee, tea, milk] | coffee     | coffee      | coffee       | NULL             | +----------------------------------+-------------+--------------+------------------*/

-- Error. Array index 6 is out of bounds.SELECT["coffee","tea","milk"][6]ASitem_offset

-- Error. Array index 6 is out of bounds.SELECT["coffee","tea","milk"][OFFSET(6)]ASitem_offset

struct_expression"["struct_subscript_specifier"]"struct_subscript_specifier:{index|position_keyword(index)}position_keyword:{OFFSET|ORDINAL}

SELECTSTRUCT<INT64,STRING,BOOL>(23,"tea",FALSE)[0]ASfield_index,STRUCT<INT64,STRING,BOOL>(23,"tea",FALSE)[OFFSET(0)]ASfield_offset,STRUCT<INT64,STRING,BOOL>(23,"tea",FALSE)[ORDINAL(1)]ASfield_ordinal/*-------------+--------------+---------------+ | field_index | field_offset | field_ordinal | +-------------+--------------+---------------+ | 23          | 23           | 23            | +-------------+--------------+---------------*/

-- Error: Field ordinal 6 is out of bounds in STRUCTSELECTSTRUCT<INT64,STRING,BOOL>(23,"tea",FALSE)[6]ASfield_offset

-- Error: Field ordinal 6 is out of bounds in STRUCTSELECTSTRUCT<INT64,STRING,BOOL>(23,"tea",FALSE)[OFFSET(6)]ASfield_offset

json_expression"["array_element_id"]"

json_expression"["field_name"]"

SELECTjson_value.class.students[0]['name']ASfirst_studentFROMUNNEST([JSON'{"class" : {"students" : [{"name" : "Jane"}]}}',JSON'{"class" : {"students" : []}}',JSON'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'])ASjson_value;/*-----------------+ | first_student   | +-----------------+ | "Jane"          | | NULL            | | "John"          | +-----------------*/

date_expression+int64_expressionint64_expression+date_expressiondate_expression-int64_expression

SELECTDATE"2020-09-22"+1ASday_later,DATE"2020-09-22"-7ASweek_ago/*------------+------------+ | day_later  | week_ago   | +------------+------------+ | 2020-09-23 | 2020-09-15 | +------------+------------*/

date_expression-date_expressiontimestamp_expression-timestamp_expressiondatetime_expression-datetime_expression

SELECTDATE"2021-05-20"-DATE"2020-04-19"ASdate_diff,TIMESTAMP"2021-06-01 12:34:56.789"-TIMESTAMP"2021-05-31 00:00:00"AStime_diff/*-------------------+------------------------+ | date_diff         | time_diff              | +-------------------+------------------------+ | 0-0 396 0:0:0     | 0-0 0 36:34:56.789     | +-------------------+------------------------*/

date_expression+interval_expression=DATETIMEdate_expression-interval_expression=DATETIMEtimestamp_expression+interval_expression=TIMESTAMPtimestamp_expression-interval_expression=TIMESTAMPdatetime_expression+interval_expression=DATETIMEdatetime_expression-interval_expression=DATETIME

SELECTDATE"2021-04-20"+INTERVAL25HOURASdate_plus,TIMESTAMP"2021-05-02 00:01:02.345+00"-INTERVAL10SECOND AStime_minus;/*-------------------------+--------------------------------+ | date_plus               | time_minus                     | +-------------------------+--------------------------------+ | 2021-04-21 01:00:00     | 2021-05-02 00:00:52.345+00     | +-------------------------+--------------------------------*/

interval_expression*integer_expression=INTERVALinterval_expression/integer_expression=INTERVAL

SELECTINTERVAL'1:2:3'HOURTOSECOND*10ASmul1,INTERVAL35SECOND*4ASmul2,INTERVAL10YEAR/3ASdiv1,INTERVAL1MONTH/12ASdiv2/*----------------+--------------+-------------+--------------+ | mul1           | mul2         | div1        | div2         | +----------------+--------------+-------------+--------------+ | 0-0 0 10:20:30 | 0-0 0 0:2:20 | 3-4 0 0:0:0 | 0-0 2 12:0:0 | +----------------+--------------+-------------+--------------*/

GoogleSQL supports theAND,OR, andNOT logical operators.Logical operators allow onlyBOOL orNULL inputand usethree-valued logicto produce a result. The result can beTRUE,FALSE, orNULL:

The order of evaluation of operands toAND andOR can vary, and evaluationcan be skipped if unnecessary.

Examples

The examples in this section reference a table calledentry_table:

/*-------+ | entry | +-------+ | a     | | b     | | c     | | NULL  | +-------*/

SELECT'a'FROMentry_tableWHEREentry='a'-- a => 'a' = 'a' => TRUE-- b => 'b' = 'a' => FALSE-- NULL => NULL = 'a' => NULL/*-------+ | entry | +-------+ | a     | +-------*/

SELECTentryFROMentry_tableWHERENOT(entry='a')-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL/*-------+ | entry | +-------+ | b     | | c     | +-------*/

SELECTentryFROMentry_tableWHEREentryISNULL-- a => 'a' IS NULL => FALSE-- b => 'b' IS NULL => FALSE-- NULL => NULL IS NULL => TRUE/*-------+ | entry | +-------+ | NULL  | +-------*/

Comparison operators

Compares operands and produces the results of the comparison as aBOOLvalue. These comparison operators are available:

The following rules apply to operands in a comparison operator:

• The operands must becomparable.
• A comparison operator generally requires both operands to be of thesame type.
• If the operands are of different types, and the values of those types can beconverted to a common type without loss of precision,they are generally coerced to that common type for the comparison.
• A literal operand is generally coerced to the same data type of anon-literal operand that's part of the comparison.
• Struct operands support only these comparison operators: equal(=), not equal (!= and<>), andIN.

The following rules apply when comparing these data types:

• FLOAT64:All comparisons withNaN returnFALSE,except for!= and<>, which returnTRUE.
• BOOL:FALSE is less thanTRUE.
• STRING: Strings are compared codepoint-by-codepoint, which means thatcanonically equivalent strings are only guaranteed to compare as equal ifthey have been normalized first.
• JSON: You can't compare JSON, but you can comparethe values inside of JSON if you convert the values toSQL values first. For more information, seeJSON functions.
• NULL: Any operation with aNULL input returnsNULL.

• STRUCT: When testing a struct for equality, it's possible that one or morefields areNULL. In such cases:

  • If all non-NULL field values are equal, the comparison returnsNULL.
  • If any non-NULL field values aren't equal, the comparison returnsFALSE.

  The following table demonstrates howSTRUCT data types are compared whenthey have fields that areNULL valued.

  Struct1	Struct2	Struct1 = Struct2
  STRUCT(1, NULL)	STRUCT(1, NULL)	NULL
  STRUCT(1, NULL)	STRUCT(2, NULL)	FALSE
  STRUCT(1,2)	STRUCT(1, NULL)	NULL

EXISTS operator

EXISTS(subquery)

Description

ReturnsTRUE if the subquery produces one or more rows. ReturnsFALSE ifthe subquery produces zero rows. Never returnsNULL. To learn more abouthow you can use a subquery withEXISTS,seeEXISTS subqueries.

Examples

In this example, theEXISTS operator returnsFALSE because there are norows inWords where the direction issouth:

WITHWordsAS(SELECT'Intend'asvalue,'east'asdirectionUNIONALLSELECT'Secure','north'UNIONALLSELECT'Clarity','west')SELECTEXISTS(SELECTvalueFROMWordsWHEREdirection='south')asresult;/*--------+ | result | +--------+ | FALSE  | +--------*/

IN operator

TheIN operator supports the following syntax:

search_value[NOT]INvalue_setvalue_set:{(expression[,...])|(subquery)|UNNEST(array_expression)}

Description

Checks for an equal value in a set of values.Semantic rules apply, but in general,IN returnsTRUEif an equal value is found,FALSE if an equal value is excluded, otherwiseNULL.NOT IN returnsFALSE if an equal value is found,TRUE if anequal value is excluded, otherwiseNULL.

• search_value: The expression that's compared to a set of values.

• value_set: One or more values to compare to a search value.

  • (expression[, ...]): A list of expressions.
  • (subquery): Asubquery that returnsa single column. The values in that column are the set of values.If no rows are produced, the set of values is empty.

  • UNNEST(array_expression): AnUNNEST operatorthat returns a column of values from an array expression. This isequivalent to:

    IN(SELECTelementFROMUNNEST(array_expression)ASelement)

This operator supportscollation, but these limitations apply:

• [NOT] IN UNNEST doesn't support collation.
• If collation is used with a list of expressions, there must be at least oneitem in the list.

Semantic rules

When using theIN operator, the following semantics apply in this order:

• ReturnsFALSE ifvalue_set is empty.
• ReturnsNULL ifsearch_value isNULL.
• ReturnsTRUE ifvalue_set contains a value equal tosearch_value.
• ReturnsNULL ifvalue_set contains aNULL.
• ReturnsFALSE.

When using theNOT IN operator, the following semantics apply in this order:

• ReturnsTRUE ifvalue_set is empty.
• ReturnsNULL ifsearch_value isNULL.
• ReturnsFALSE ifvalue_set contains a value equal tosearch_value.
• ReturnsNULL ifvalue_set contains aNULL.
• ReturnsTRUE.

The semantics of:

xIN(y,z,...)

are defined as equivalent to:

(x=y)OR(x=z)OR...

and the subquery and array forms are defined similarly.

xNOTIN...

is equivalent to:

NOT(xIN...)

TheUNNEST form treats an array scan likeUNNEST in theFROM clause:

x[NOT]INUNNEST(<arrayexpression>)

This form is often used with array parameters. For example:

xINUNNEST(@array_parameter)

See theArrays topic for more informationon how to use this syntax.

IN can be used with multi-part keys by using the struct constructor syntax.For example:

(Key1,Key2)IN((12,34),(56,78))(Key1,Key2)IN(SELECT(table.a,table.b)FROMtable)

See theStruct Type topic for more information.

Return Data Type

BOOL

Examples

You can use theseWITH clauses to emulate temporary tables forWords andItems in the following examples:

WITHWordsAS(SELECT'Intend'asvalueUNIONALLSELECT'Secure'UNIONALLSELECT'Clarity'UNIONALLSELECT'Peace'UNIONALLSELECT'Intend')SELECT*FROMWords;/*----------+ | value    | +----------+ | Intend   | | Secure   | | Clarity  | | Peace    | | Intend   | +----------*/

WITHItemsAS(SELECTSTRUCT('blue'AScolor,'round'ASshape)ASinfoUNIONALLSELECTSTRUCT('blue','square')UNIONALLSELECTSTRUCT('red','round'))SELECT*FROMItems;/*----------------------------+ | info                       | +----------------------------+ | {blue color, round shape}  | | {blue color, square shape} | | {red color, round shape}   | +----------------------------*/

Example withIN and an expression:

SELECT*FROMWordsWHEREvalueIN('Intend','Secure');/*----------+ | value    | +----------+ | Intend   | | Secure   | | Intend   | +----------*/

Example withNOT IN and an expression:

SELECT*FROMWordsWHEREvalueNOTIN('Intend');/*----------+ | value    | +----------+ | Secure   | | Clarity  | | Peace    | +----------*/

Example withIN, a scalar subquery, and an expression:

SELECT*FROMWordsWHEREvalueIN((SELECT'Intend'),'Clarity');/*----------+ | value    | +----------+ | Intend   | | Clarity  | | Intend   | +----------*/

Example withIN and anUNNEST operation:

SELECT*FROMWordsWHEREvalueINUNNEST(['Secure','Clarity']);/*----------+ | value    | +----------+ | Secure   | | Clarity  | +----------*/

Example withIN and a struct:

SELECT(SELECTASSTRUCTItems.info)asitemFROMItemsWHERE(info.shape,info.color)IN(('round','blue'));/*------------------------------------+ | item                               | +------------------------------------+ | { {blue color, round shape} info } | +------------------------------------*/

IS operators

IS operators return TRUE or FALSE for the condition they are testing. They neverreturnNULL, even forNULL inputs, unlike theIS_INF andIS_NANfunctions defined inMathematical Functions.IfNOT is present, the outputBOOL value isinverted.

IS DISTINCT FROM operator

expression_1IS[NOT]DISTINCTFROMexpression_2

Description

IS DISTINCT FROM returnsTRUE if the input values are considered to bedistinct from each other by theDISTINCT andGROUP BY clauses. Otherwise, returnsFALSE.

a IS DISTINCT FROM b beingTRUE is equivalent to:

• SELECT COUNT(DISTINCT x) FROM UNNEST([a,b]) x returning2.
• SELECT * FROM UNNEST([a,b]) x GROUP BY x returning 2 rows.

a IS DISTINCT FROM b is equivalent toNOT (a = b), except for thefollowing cases:

• This operator never returnsNULL soNULL values are considered to bedistinct from non-NULL values, not otherNULL values.
• NaN values are considered to be distinct from non-NaN values, but nototherNaN values.

You can use this operation with fields in a complex data type, but not onthe complex data types themselves. These complex data types can't be compareddirectly:

• STRUCT
• ARRAY

Input values:

• expression_1: The first value to compare. This can be a groupable data type,NULL orNaN.
• expression_2: The second value to compare. This can be a groupabledata type,NULL orNaN.
• NOT: If present, the outputBOOL value is inverted.

Return type

BOOL

Examples

These returnTRUE:

SELECT1ISDISTINCTFROM2

SELECT1ISDISTINCTFROMNULL

SELECT1ISNOTDISTINCTFROM1

SELECTNULLISNOTDISTINCTFROMNULL

These returnFALSE:

SELECTNULLISDISTINCTFROMNULL

SELECT1ISDISTINCTFROM1

SELECT1ISNOTDISTINCTFROM2

SELECT1ISNOTDISTINCTFROMNULL

LIKE operator

expression_1[NOT]LIKEexpression_2

Description

LIKE returnsTRUE if the string in the first operandexpression_1matches a pattern specified by the second operandexpression_2,otherwise returnsFALSE.

NOT LIKE returnsTRUE if the string in the first operandexpression_1doesn't match a pattern specified by the second operandexpression_2,otherwise returnsFALSE.

Expressions can contain these characters:

• A percent sign (%) matches any number of characters or bytes.
• An underscore (_) matches a single character or byte.
• You can escape\,_, or% using two backslashes. For example,\\%. If you are using raw strings, only a single backslash isrequired. For example,r'\%'.

This operator supportscollation, but caveats apply:

• Each% character inexpression_2 represents anarbitrary string specifier. An arbitrary string specifier can representany sequence of0 or more characters.

• A character in the expression represents itself and is considered asingle character specifier unless:

  • The character is a percent sign (%).

  • The character is an underscore (_) and the collator isn'tund:ci.

• These additional rules apply to the underscore (_) character:

  • If the collator isn'tund:ci, an error is produced when an underscoreisn't escaped inexpression_2.

  • If the collator isn'tund:ci, the underscore isn't allowed when theoperands have collation specified.

  • Somecompatibility composites, such as the fi-ligature (fi) and thetelephone sign (℡), will produce a match if they are compared to anunderscore.

  • A single underscore matches the idea of what a character is, based onan approximation known as agrapheme cluster.

• For a contiguous sequence of single character specifiers, equalitydepends on the collator and its language tags and tailoring.

  • By default, theund:ci collator doesn't fully normalize a string.Some canonically equivalent strings are considered unequal forboth the= andLIKE operators.

  • TheLIKE operator with collation has the same behavior as the=operator when there are no wildcards in the strings.

  • Character sequences with secondary or higher-weighted differences areconsidered unequal. This includes accent differences and somespecial cases.

    For example there are three ways to produce German sharpß:

    • \u1E9E
    • \U00DF
    • ss

    \u1E9E and\U00DF are considered equal but differ in tertiary.They are considered equal withund:ci collation but different fromss, which has secondary differences.

  • Character sequences with tertiary or lower-weighted differences areconsidered equal. This includes case differences andkana subtype differences, which are considered equal.

• There areignorable characters defined in Unicode.Ignorable characters are ignored in the pattern matching.

Return type

BOOL

Examples

The following examples illustrate how you can check to see if the string in thefirst operand matches a pattern specified by the second operand.

-- Returns TRUESELECT'apple'LIKE'a%';

-- Returns FALSESELECT'%a'LIKE'apple';

-- Returns FALSESELECT'apple'NOTLIKE'a%';

-- Returns TRUESELECT'%a'NOTLIKE'apple';

-- Produces an errorSELECTNULLLIKE'a%';

-- Produces an errorSELECT'apple'LIKENULL;

The following example illustrates how to search multiple patterns in an arrayto find a match with theLIKE operator:

WITHWordsAS(SELECT'Intend with clarity.'asvalueUNIONALLSELECT'Secure with intention.'UNIONALLSELECT'Clarity and security.')SELECTvalueFROMWordsWHEREEXISTS(SELECTvalueFROMUNNEST(['%ity%','%and%'])ASpatternWHEREvalueLIKEpattern);/*------------------------+ | value                  | +------------------------+ | Intend with clarity.   | | Clarity and security.  | +------------------------*/

The following examples illustrate how collation can be used with theLIKEoperator.

-- Returns FALSE'Foo'LIKE'%foo%'

-- Returns TRUECOLLATE('Foo','und:ci')LIKECOLLATE('%foo%','und:ci');

-- Returns TRUECOLLATE('Foo','und:ci')=COLLATE('foo','und:ci');

-- Produces an errorCOLLATE('Foo','und:ci')LIKECOLLATE('%foo%','binary');

-- Produces an errorCOLLATE('Foo','und:ci')LIKECOLLATE('%f_o%','und:ci');

-- Returns TRUECOLLATE('Foo_','und:ci')LIKECOLLATE('%foo\\_%','und:ci');

There are two capital forms ofß. We can use eitherSS orẞ as uppercase. While the difference betweenß andẞ is case difference (tertiarydifference), the difference between sharps andss is secondary andconsidered not equal using theund:ci collator. For example:

-- Returns FALSE'MASSE'LIKE'Maße';

-- Returns FALSECOLLATE('MASSE','und:ci')LIKE'%Maße%';

-- Returns FALSECOLLATE('MASSE','und:ci')=COLLATE('Maße','und:ci');

The kana differences in Japanese are considered as tertiary or quaternarydifferences, and should be considered as equal in theund:ci collator withsecondary strength.

• '\u3042' is'あ' (hiragana)
• '\u30A2' is'ア' (katakana)

For example:

-- Returns FALSE'\u3042'LIKE'%\u30A2%';

-- Returns TRUECOLLATE('\u3042','und:ci')LIKECOLLATE('%\u30A2%','und:ci');

-- Returns TRUECOLLATE('\u3042','und:ci')=COLLATE('\u30A2','und:ci');

When comparing two strings, theund:ci collator compares the collation unitsbased on the specification of the collation. Even though the number ofcode points is different, the two strings are considered equal when thecollation units are considered the same.

• '\u0041\u030A' is'Å' (two code points)
• '\u0061\u030A' is'å' (two code points)
• '\u00C5' is'Å' (one code point)

In the following examples, the difference between'\u0061\u030A' and'\u00C5' is tertiary.

-- Returns FALSE'\u0061\u030A'LIKE'%\u00C5%';

-- Returns TRUECOLLATE('\u0061\u030A','und:ci')LIKE'%\u00C5%';

-- Returns TRUECOLLATE('\u0061\u030A','und:ci')=COLLATE('\u00C5','und:ci');

In the following example,'\u0083' is aNO BREAK HERE character andis ignored.

-- Returns FALSE'\u0083'LIKE'';

-- Returns TRUECOLLATE('\u0083','und:ci')LIKE'';

QuantifiedLIKE operator

The quantifiedLIKE operator supports the following syntax:

search_value[NOT]LIKEquantifierpatternsquantifier:{ANY|SOME|ALL}patterns:{pattern_expression_list|pattern_array}pattern_expression_list:(expression[,...])pattern_array:UNNEST(array_expression)

Description

Checkssearch_value for matches against several patterns. Each comparison iscase-sensitive. Wildcard searches are supported.Semantic rules apply, but in general,LIKEreturnsTRUE if a matching pattern is found,FALSE if a matching patternisn't found, or otherwiseNULL.NOT LIKE returnsFALSE if amatching pattern is found,TRUE if a matching pattern isn't found, orotherwiseNULL.

• search_value: The value to search for matching patterns. This value can be aSTRING orBYTES type.

• patterns: The patterns to look for in the search value. Each pattern mustresolve to the same type assearch_value.

  • pattern_expression_list: A list of one or more patterns that match thesearch_value type.

  • pattern_array: AnUNNESToperation that returns a column of values withthe same type assearch_value from an array expression.

  The regular expressions that are supported by theLIKE operator are also supported bypatterns in thequantifiedLIKE operator.

• quantifier: Condition for pattern matching.

  • ANY: Checks if the set of patterns contains at least one pattern thatmatches the search value.

  • SOME: Synonym forANY.

  • ALL: Checks if every pattern in the set of patterns matches thesearch value.

Collation caveats

Collation is supported, but with the following caveats:

• The collation caveats that apply to theLIKE operator alsoapply to the quantifiedLIKE operator.
• If a collation-supported input contains no collation specification or anempty collation specification and another input contains an explicitly definedcollation, the explicitly defined collation is used for all of the inputs.
• All inputs with a non-empty, explicitly defined collation specification musthave the same type of collation specification, otherwise an error is thrown.

Semantics rules

When using the quantifiedLIKE operator withANY orSOME, thefollowing semantics apply in this order:

• ReturnsFALSE ifpatterns is empty.
• ReturnsNULL ifsearch_value isNULL.
• ReturnsTRUE ifsearch_value matches at least one value inpatterns.
• ReturnsNULL if a pattern inpatterns isNULL.
• ReturnsFALSE.

When using the quantifiedLIKE operator withALL, the following semanticsapply in this order:

• ReturnsTRUE ifpatterns is empty.
• ReturnsNULL ifsearch_value isNULL.
• ReturnsFALSE ifsearch_value LIKE pattern isFALSE for at least one value inpatterns.
• ReturnsNULL if a pattern inpatterns isNULL.
• ReturnsTRUE.

When using the quantifiedNOT LIKE operator withANY orSOME, thefollowing semantics apply in this order:

• ReturnsFALSE ifpatterns is empty.
• ReturnsNULL ifsearch_value isNULL.
• ReturnsTRUE ifsearch_value LIKE pattern isFALSE for at least one value inpatterns.
• ReturnsNULL if a pattern inpatterns isNULL.
• ReturnsFALSE.

When using the quantifiedNOT LIKE operator withALL, the followingsemantics apply in this order:

• ReturnsTRUE ifpatterns is empty.
• Forpattern_array, returnsTRUE ifpatterns is empty.
• ReturnsNULL ifsearch_value isNULL.
• ReturnsFALSE ifsearch_value matches at least one value inpatterns.
• ReturnsNULL if a pattern inpatterns isNULL.
• ReturnsTRUE.

Return Data Type

BOOL

Examples

The following example checks to see if theIntend% or%intention%pattern exists in a value and produces that value if either pattern is found:

WITHWordsAS(SELECT'Intend with clarity.'asvalueUNIONALLSELECT'Secure with intention.'UNIONALLSELECT'Clarity and security.')SELECT*FROMWordsWHEREvalueLIKEANY('Intend%','%intention%');/*------------------------+ | value                  | +------------------------+ | Intend with clarity.   | | Secure with intention. | +------------------------*/

The following example checks to see if the%ity%pattern exists in a value and produces that value if the pattern is found.

Example withLIKE ALL:

WITHWordsAS(SELECT'Intend with clarity.'asvalueUNIONALLSELECT'Secure with intention.'UNIONALLSELECT'Clarity and security.')SELECT*FROMWordsWHEREvalueLIKEALL('%ity%');/*-----------------------+ | value                 | +-----------------------+ | Intend with clarity.  | | Clarity and security. | +-----------------------*/

The following example checks to see if the%ity%pattern exists in a value produces that value if the patternisn't found:

WITHWordsAS(SELECT'Intend with clarity.'asvalueUNIONALLSELECT'Secure with intention.'UNIONALLSELECT'Clarity and security.')SELECT*FROMWordsWHEREvalueNOTLIKE('%ity%');/*------------------------+ | value                  | +------------------------+ | Secure with intention. | +------------------------*/

You can pass in an array forpatterns. For example:

WITHWordsAS(SELECT'Intend with clarity.'asvalueUNIONALLSELECT'Secure with intention.'UNIONALLSELECT'Clarity and security.')SELECT*FROMWordsWHEREvalueLIKEANYUNNEST(['%ion%','%and%']);/*------------------------+ | value                  | +------------------------+ | Secure with intention. | | Clarity and security.  | +------------------------*/

The following queries illustrate some of the semantic rules for thequantifiedLIKE operator:

SELECTNULLLIKEANY('a','b'),-- NULL'a'LIKEANY('a','c'),-- TRUE'a'LIKEANY('b','c'),-- FALSE'a'LIKEANY('a',NULL),-- TRUE'a'LIKEANY('b',NULL),-- NULLNULLNOTLIKEANY('a','b'),-- NULL'a'NOTLIKEANY('a','b'),-- TRUE'a'NOTLIKEANY('a','%a%'),-- FALSE'a'NOTLIKEANY('a',NULL),-- NULL'a'NOTLIKEANY('b',NULL);-- TRUE

SELECTNULLLIKESOME('a','b'),-- NULL'a'LIKESOME('a','c'),-- TRUE'a'LIKESOME('b','c'),-- FALSE'a'LIKESOME('a',NULL),-- TRUE'a'LIKESOME('b',NULL),-- NULLNULLNOTLIKESOME('a','b'),-- NULL'a'NOTLIKESOME('a','b'),-- TRUE'a'NOTLIKESOME('a','%a%'),-- FALSE'a'NOTLIKESOME('a',NULL),-- NULL'a'NOTLIKESOME('b',NULL);-- TRUE

SELECTNULLLIKEALL('a','b'),-- NULL'a'LIKEALL('a','%a%'),-- TRUE'a'LIKEALL('a','c'),-- FALSE'a'LIKEALL('a',NULL),-- NULL'a'LIKEALL('b',NULL),-- FALSENULLNOTLIKEALL('a','b'),-- NULL'a'NOTLIKEALL('b','c'),-- TRUE'a'NOTLIKEALL('a','c'),-- FALSE'a'NOTLIKEALL('a',NULL),-- FALSE'a'NOTLIKEALL('b',NULL);-- NULL

The following queries illustrate some of the semantic rules for thequantifiedLIKE operator and collation:

SELECTCOLLATE('a','und:ci')LIKEALL('a','A'),-- TRUE'a'LIKEALL(COLLATE('a','und:ci'),'A'),-- TRUE'a'LIKEALL('%A%',COLLATE('a','und:ci'));-- TRUE

-- ERROR: BYTES and STRING values can't be used together.SELECTb'a'LIKEALL(COLLATE('a','und:ci'),'A');

Concatenation operator

The concatenation operator combines multiple values into one.

WITH expression

WITH(variable_assignment[,...],result_expression)variable_assignment:variable_nameASexpression

Description

Creates one or more variables. Each variable can be used in subsequentexpressions within theWITH expression. Returns the value ofresult_expression.

• variable_assignment: Introduces a variable. The variable name must beunique within a givenWITH expression. Each expression can reference thevariables that come before it. For example, if you create variablea,then follow it with variableb, then you can referencea inside of theexpression forb.

  • variable_name: The name of the variable.

  • expression: The value to assign to the variable.

• result_expression: An expression that can use all of the variables definedbefore it. The value ofresult_expression is returned by theWITHexpression.

Return Type

• The type of theresult_expression.

Requirements and Caveats

• A variable can only be assigned once within aWITH expression.
• Variables created duringWITH may not be usedin analytic or aggregatefunction arguments. For example,WITH(a AS ..., SUM(a)) produces an error.
• Each variable's expression is evaluated only once.

Examples

The following example first concatenates variablea withb, then variableb withc:

SELECTWITH(aAS'123',-- a is '123'bASCONCAT(a,'456'),-- b is '123456'cAS'789',-- c is '789'CONCAT(b,c))ASresult;-- b + c is '123456789'/*-------------+ | result      | +-------------+ | '123456789' | +-------------*/

In the following example, the volatile expressionRAND() is evaluated once.The value of the result expression is always0.0:

SELECTWITH(aASRAND(),a-a);/*---------+ | result  | +---------+ | 0.0     | +---------*/

Aggregate or analytic functionresults can be stored in variables.

SELECTWITH(sASSUM(input),cASCOUNT(input),s/c)FROMUNNEST([1.0,2.0,3.0])ASinput;/*---------+ | result  | +---------+ | 2.0     | +---------*/

Variables can't be used in aggregate oranalytic function call arguments.

SELECTWITH(diffASa-b,AVG(diff))FROMUNNEST([STRUCT(1ASa,2ASb),STRUCT(3ASa,4ASb),STRUCT(5ASa,6ASb)]);-- ERROR: WITH variables like 'diff' can't be used in aggregate or analytic-- function arguments.

AWITH expression is different from aWITH clause. The following exampleshows a query that uses both:

WITHmy_tableAS(SELECT1ASx,2ASyUNIONALLSELECT3ASx,4ASyUNIONALLSELECT5ASx,6ASy)SELECTWITH(aASSUM(x),bASCOUNT(x),a/b)ASavg_x,AVG(y)ASavg_yFROMmy_tableWHEREx >1;/*-------+-------+ | avg_x | avg_y | +-------+-------+ | 4     | 5     | +-------+-------*/