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

Showing content from https://docs.snowflake.com/en/sql-reference/data-types-logical below:

Website Navigation

• Reference
• SQL data types reference

Logical data types | Snowflake Documentation

This topic describes the logical data types supported in Snowflake.

Snowflake supports a single logical data type (BOOLEAN).

BOOLEAN can have TRUE or FALSE values. BOOLEAN can also have an UNKNOWN value, which is represented by NULL. BOOLEAN columns can be used in expressions (for example, a SELECT list), as well as predicates (for example, a WHERE clause).

The BOOLEAN data type enables support for Ternary logic.

Snowflake supports conversion to and from BOOLEAN.

Non-BOOLEAN values can be converted to BOOLEAN values explicitly or implicitly.

You can explicitly convert specific text string and numeric values to BOOLEAN values by using the TO_BOOLEAN or CAST functions:

String conversion:

• Strings converted to TRUE: 'true', 't', 'yes', 'y', 'on', '1'.

• Strings converted to FALSE: 'false', 'f', 'no', 'n', 'off', '0'.

• Conversion is case-insensitive.

• Other text strings can’t be converted to BOOLEAN values.

Numeric conversion:

• Zero (0) is converted to FALSE.

• Any non-zero value is converted to TRUE.

Snowflake can implicitly convert specific text string and numeric values to BOOLEAN values:

String conversion:

• 'true' is converted to TRUE.

• 'false' is converted to FALSE.

• Conversion is case-insensitive.

Numeric conversion:

• Zero (0) is converted to FALSE.

• Any non-zero value is converted to TRUE.

BOOLEAN values can be converted to non-BOOLEAN values explicitly or implicitly.

You can explicitly cast BOOLEAN values to text string or numeric values:

String conversion:

• TRUE is converted to 'true'.

• FALSE is converted to 'false'.

Numeric conversion:

• TRUE is converted to 1.

• FALSE is converted to 0.

Snowflake can implicitly convert BOOLEAN values to text string values:

String conversion:

• TRUE is converted to 'true'.

• FALSE is converted to 'false'.

Create a table and insert values:

CREATE OR REPLACE TABLE test_boolean(
  b BOOLEAN,
  n NUMBER,
  s STRING);

INSERT INTO test_boolean VALUES
  (true, 1, 'yes'),
  (false, 0, 'no'),
  (NULL, NULL, NULL);

SELECT * FROM test_boolean;

Copy

+-------+------+------+
| B     |    N | S    |
|-------+------+------|
| True  |    1 | yes  |
| False |    0 | no   |
| NULL  | NULL | NULL |
+-------+------+------+

The following query includes a BOOLEAN-typed expression:

SELECT b, n, NOT b AND (n < 1) FROM test_boolean;

Copy

+-------+------+-------------------+
| B     |    N | NOT B AND (N < 1) |
|-------+------+-------------------|
| True  |    1 | False             |
| False |    0 | True              |
| NULL  | NULL | NULL              |
+-------+------+-------------------+

The following example uses a BOOLEAN column in predicates:

SELECT * FROM test_boolean WHERE NOT b AND (n < 1);

Copy

+-------+---+----+
| B     | N | S  |
|-------+---+----|
| False | 0 | no |
+-------+---+----+

The following example casts a text value to a BOOLEAN value. The example uses the SYSTEM$TYPEOF to show the type of the value after the conversion.

SELECT s,
       TO_BOOLEAN(s),
       SYSTEM$TYPEOF(TO_BOOLEAN(s))
  FROM test_boolean;

Copy

+------+---------------+------------------------------+
| S    | TO_BOOLEAN(S) | SYSTEM$TYPEOF(TO_BOOLEAN(S)) |
|------+---------------+------------------------------|
| yes  | True          | BOOLEAN[SB1]                 |
| no   | False         | BOOLEAN[SB1]                 |
| NULL | NULL          | BOOLEAN[SB1]                 |
+------+---------------+------------------------------+

The following example casts a number value to a BOOLEAN value:

SELECT n,
       TO_BOOLEAN(n),
       SYSTEM$TYPEOF(TO_BOOLEAN(n))
  FROM test_boolean;

Copy

+------+---------------+------------------------------+
| N    | TO_BOOLEAN(N) | SYSTEM$TYPEOF(TO_BOOLEAN(N)) |
|------+---------------+------------------------------|
| 1    | True          | BOOLEAN[SB1]                 |
| 0    | False         | BOOLEAN[SB1]                 |
| NULL | NULL          | BOOLEAN[SB1]                 |
+------+---------------+------------------------------+

In this example, Snowflake implicitly converts a BOOLEAN value to a text value:

SELECT 'Text for ' || s || ' is ' || b AS result,
       SYSTEM$TYPEOF('Text for ' || s || ' is ' || b) AS type_of_result
  FROM test_boolean;

Copy

+----------------------+-------------------------+
| RESULT               | TYPE_OF_RESULT          |
|----------------------+-------------------------|
| Text for yes is true | VARCHAR(134217728)[LOB] |
| Text for no is false | VARCHAR(134217728)[LOB] |
| NULL                 | VARCHAR(134217728)[LOB] |
+----------------------+-------------------------+

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