数据类型转换¶

显式类型转换与隐式类型转换¶

用户可以将值从一种数据类型显式转换为另一种数据类型。这称为 显式类型转换。

在某些情况下，Snowflake 会自动将值转换为另一种数据类型。这称为 隐式类型转换 或 强制转换。

类型转换和优先级¶

在表达式内进行类型转换时，代码必须考虑转换运算符相对于表达式中其他运算符的优先级。

请参考以下示例：

SELECT height * width::VARCHAR || ' square meters'
  FROM dimensions;

The cast operator has higher precedence than the arithmetic operator * (multiply), so the statement is interpreted as shown in the following example:

... height * (width::VARCHAR) ...

To cast the result of the expression height * width, use parentheses, as shown in the following example:

SELECT (height * width)::VARCHAR || ' square meters'
  FROM dimensions;

又例如，请注意以下语句：

SELECT -0.0::FLOAT::BOOLEAN;

You might expect this to be interpreted as shown in the following example:

SELECT (-0.0::FLOAT)::BOOLEAN;

因此，预计会返回 FALSE (0 = FALSE,1 = TRUE)。

However, the cast operator has higher precedence than the unary minus (negation) operator, so the statement is interpreted as shown in the following example:

SELECT -(0.0::FLOAT::BOOLEAN);

因此，查询会导致产生错误消息，因为一元减号不能应用于 BOOLEAN。

可转换的数据类型¶

下表显示了 Snowflake 中的有效数据类型转换。该表还显示了 Snowflake 可以自动执行的强制转换。

For more information about conversions between semi-structured types and structured types, see 转换结构化类型和半结构化类型.

使用说明¶

除非另有说明，否则以下规则适用于显式类型转换和隐式类型转换：

• Conversion depends not only on the data type, but also the value, of the source; for example:

  • VARCHAR 值 '123' 可以转换为数值，但 VARCHAR 值 'xyz' 不能转换为数值。

  • The ability to cast a specific value of type VARIANT depends on the type of the data inside the VARIANT. For example, if the VARIANT contains a value of type TIME, then you can't cast the VARIANT value to a TIMESTAMP value, because you can't cast a TIME value to a TIMESTAMP value.

• 如果可能，请传入相同类型的实参。避免传入不同类型的实参。

• 如果其中一个实参是数字，则函数会 强制转换 非数值字符串实参（例如 'a string'）和不是类型 NUMBER(18,5) 常量的字符串实参。

  对于不是常量的数值字符串实参，如果 NUMBER(18,5) 不足以表示数值，则将实参 转换 为可以表示该值的类型。

• For some pairs of data types, conversion can result in loss of precision; for example:

  • 将 FLOAT 值转换为 INTEGER 值将对该值进行舍入。

  • Converting a value from fixed-point numeric --- for example, NUMBER(38, 0) --- to floating point --- for example, FLOAT --- can result in rounding or truncation if the fixed-point number can't be precisely represented in a floating point number.

  • 将 TIMESTAMP 值转换为 DATE 值将移除有关一天中时间的信息。

• Although Snowflake converts values in some situations where loss of precision can occur, Snowflake doesn't allow conversion in other situations where a loss of precision would occur. For example, Snowflake doesn't allow conversion when conversion would cause the following situations to happen:

  • 截断 VARCHAR 值。例如，Snowflake 不会将 VARCHAR(10) 隐式或显式转换为 VARCHAR(5)。

  • Result in the loss of digits other than the least significant digits. For example, the following loss of digits fails:

    SELECT 12.3::FLOAT::NUMBER(3,2);
    

    Copy

    在此示例中，数字 12.3 在小数点之前有两位数字，但数据类型 NUMBER(3,2) 在小数点之前只有一位数字的空间。

• 从精度较低的类型转换为精度较高的类型时，转换会使用默认值。例如，将 DATE 值转换为 TIMESTAMP_NTZ 值会导致小时、分钟、秒和小数秒值设置为 0。

• 当 FLOAT 值被转换为 VARCHAR 值时，尾零将省略。

  For example, the following statements create a table and insert a row that contains a VARCHAR value, a FLOAT value, and a VARIANT value. The VARIANT value is constructed from JSON that contains a floating-point value represented with trailing zeros:

  CREATE OR REPLACE TABLE convert_test_zeros (
    varchar1 VARCHAR,
    float1 FLOAT,
    variant1 VARIANT);
  
  INSERT INTO convert_test_zeros SELECT
    '5.000',
    5.000,
    PARSE_JSON('{"Loan Number": 5.000}');
  

  Copy

  以下 SELECT 语句将 VARIANT 列中的 FLOAT 列和 FLOAT 值显式转换为 VARCHAR。在每种情况下，VARCHAR 均不包含尾零：

  SELECT varchar1,
         float1::VARCHAR,
         variant1:"Loan Number"::VARCHAR
    FROM convert_test_zeros;
  

  Copy

  +----------+-----------------+---------------------------------+
  | VARCHAR1 | FLOAT1::VARCHAR | VARIANT1:"LOAN NUMBER"::VARCHAR |
  |----------+-----------------+---------------------------------|
  | 5.000    | 5               | 5                               |
  +----------+-----------------+---------------------------------+
  

• Some operations can return different data types, depending on a conditional expression. For example, the following IFNULL calls return slightly different data types depending on the input values:

  SELECT SYSTEM$TYPEOF(IFNULL(12.3, 0)),
         SYSTEM$TYPEOF(IFNULL(NULL, 0));
  

  Copy

  +--------------------------------+--------------------------------+
  | SYSTEM$TYPEOF(IFNULL(12.3, 0)) | SYSTEM$TYPEOF(IFNULL(NULL, 0)) |
  |--------------------------------+--------------------------------|
  | NUMBER(3,1)[SB1]               | NUMBER(1,0)[SB1]               |
  +--------------------------------+--------------------------------+
  

  If the expression has more than one possible data type, Snowflake chooses the data type based on the actual result. For more information about precision and scale in calculations, see 算术运算的小数位数和精度. If the query generates more than one result --- for example, multiple rows of results --- Snowflake chooses a data type that is capable of holding each of the individual results.

• Some applications, such as SnowSQL, and some graphical user interfaces, such as the Classic Console, apply their own conversion and formatting rules when they display data. For example, SnowSQL displays BINARY values as a string that contains only hexadecimal digits; that string is generated by implicitly calling a conversion function. Therefore, the data that SnowSQL displays might not unambiguously indicate which data conversions that Snowflake coerced.