Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/array_contains below:

Website Navigation

• Reference
• Function and stored procedure reference
• Semi-structured and structured data

ARRAY_CONTAINS | Snowflake Documentation

Categories:

Semi-structured and structured data functions (Array/Object)

Returns TRUE if the specified value is found in the specified array.

ARRAY_CONTAINS( <value_expr> , <array> )

Copy

value_expr

Value to find in array.

• If array is a semi-structured array, value_expr must evaluate to a VARIANT.

• If array is a structured array, value_expr must evaluate to a type that is comparable to the type of the array.

array

The array to search.

This function returns a value of BOOLEAN type or NULL:

• The function returns TRUE if value_expr is present in array, including the following cases:

  • When the value_expr argument is NULL and there is a SQL NULL value in the array (undefined).

  • When the value_expr argument is JSON null and there is a JSON null value in the array (null).

• The function returns FALSE if value_expr isn’t present in array, including when the value_expr argument is JSON null and there are no JSON null values in the array.

• The function returns NULL if the value_expr argument is NULL and there are no SQL NULL values in the array.

For more information about NULL values in arrays, see NULL values.

• The function does not support wildcards in value_expr. However, you can use the ARRAY_TO_STRING function to convert an array to a string, then search the string with wildcard characters. For example, you can specify wildcards to search the returned string using the [ NOT ] LIKE and REGEXP_LIKE functions.

• If array is a semi-structured array, explicit casting of the value_expr value to a VARIANT value is required for values of the following data types:

  • String & binary

  • Date & time

  The following example explicitly casts a string value to a VARIANT value:

  SELECT ARRAY_CONTAINS('mystring2'::VARIANT, ARRAY_CONSTRUCT('mystring1', 'mystring2'));
  

  Copy

  Explicit casting isn’t required for values of other data types.

The following queries use the ARRAY_CONTAINS function in a SELECT list.

In this example, the function returns TRUE because the value_expr argument is 'hello' and the array contains a VARIANT value that stores the string 'hello':

SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hello', 'hi'));

Copy

+------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) |
|------------------------------------------------------------------|
| True                                                             |
+------------------------------------------------------------------+

In this example, the function returns FALSE because the value_expr argument is 'hello' but the array doesn’t contain a VARIANT value that stores the string 'hello':

SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hola', 'bonjour'));

Copy

+----------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------------------|
| False                                                                |
+----------------------------------------------------------------------+

In this example, the function returns NULL because the value_expr argument is NULL but the array doesn’t contain a SQL NULL value:

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', 'bonjour'));

Copy

+----------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------|
| NULL                                                     |
+----------------------------------------------------------+

In this example, the function returns TRUE because the value_expr argument is NULL and the array contains a SQL NULL value:

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', NULL));

Copy

+-----------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', NULL)) |
|-----------------------------------------------------|
| True                                                |
+-----------------------------------------------------+

In this example, the function returns TRUE because the value_expr argument is a JSON null value and the array contains a JSON null value:

SELECT ARRAY_CONTAINS(PARSE_JSON('null'), ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));

Copy

+---------------------------------------------------------------------------------+
| ARRAY_CONTAINS(PARSE_JSON('NULL'), ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|---------------------------------------------------------------------------------|
| True                                                                            |
+---------------------------------------------------------------------------------+

In this example, the function returns NULL because the value_expr argument is NULL but the array doesn’t contain a SQL NULL value (although it does contain a JSON null value):

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));

Copy

+-------------------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|-------------------------------------------------------------------|
| NULL                                                              |
+-------------------------------------------------------------------+

The following query uses the ARRAY_CONTAINS function in a WHERE clause. First, create a table with an ARRAY column and insert data:

CREATE OR REPLACE TABLE array_example (id INT, array_column ARRAY);

INSERT INTO array_example (id, array_column)
  SELECT 1, ARRAY_CONSTRUCT(1, 2, 3);

INSERT INTO array_example (id, array_column)
  SELECT 2, ARRAY_CONSTRUCT(4, 5, 6);

SELECT * FROM array_example;

Copy

+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
|  1 | [            |
|    |   1,         |
|    |   2,         |
|    |   3          |
|    | ]            |
|  2 | [            |
|    |   4,         |
|    |   5,         |
|    |   6          |
|    | ]            |
+----+--------------+

Run a query that specifies the value to find for value_expr and the ARRAY column for array:

SELECT * FROM array_example WHERE ARRAY_CONTAINS(5, array_column);

Copy

+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
|  2 | [            |
|    |   4,         |
|    |   5,         |
|    |   6          |
|    | ]            |
+----+--------------+

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4