cast 函数

适用于:勾选“是” Databricks SQL 勾选“是” Databricks Runtime

将值 expr 强制转换为目标数据类型 type

语法

cast(sourceExpr AS targetType)

参数

  • sourceExpr:任何可强制转换的表达式。
  • targetType:结果的数据类型。

返回

结果为类型 targetType

以下数据类型强制转换组合有效:

源(行)目标(列) VOID numeric STRING DATE TIMESTAMP year-month interval day-time interval BOOLEAN BINARY ARRAY MAP STRUCT
VOID Y Y Y Y Y Y Y Y Y Y Y Y
numeric N Y Y N Y Y Y Y N N N N
STRING N Y Y Y Y Y Y Y Y N N N
DATE N N Y Y Y N N N N N N N
TIMESTAMP N Y Y Y Y N N N N N N N
year-month interval N Y Y N N Y N N N N N N
day-time interval N Y Y N N N Y N N N N N
BOOLEAN N Y Y N Y N N Y N N N N
BINARY N Y Y N N N N N Y N N N
ARRAY N N Y N N N N N N Y N N
MAP N N Y N N N N N N N Y N
STRUCT N N Y N N N N N N N N Y

基于 targetType 的规则和限制

警告

在 Databricks Runtime 中,如果 spark.sql.ansi.enabledfalse,则溢出不会导致错误,而是将结果“换行”。

targetType 使用格式无效或包含无效字符的 sourceExpr 值将导致 NULL

numeric

如果 targetType数字,而 sourceExpr 的类型为:

  • VOID

    结果是指定数字类型的 NULL。

  • 数字

    如果 targetType整数,则结果将sourceExpr截断为整数。

    否则,结果将sourceExpr四舍五入以符合 targetType 的适用标量。

    如果值超出 targetType 的范围,则引发溢出错误。

    使用 try_cast 将溢出错误转换为 NULL

  • 字符串

    sourceExpr 将读取为 targetType 文本值。

    如果 sourceExpr 不符合文本值的格式,则引发错误。

    如果值超出 targetType 的范围,则引发溢出错误。

    使用 try_cast 将溢出和无效格式错误转换为 NULL

  • 时间戳

    结果是 1970-01-01 00:00:00 UTCsourceExpr 之间已消逝的秒数。

    如果 targetType整数,则结果将截断为整数。

    否则,结果将四舍五入以符合 targetType 的适用标量。

    如果结果超出 targetType 的范围,则引发溢出错误。

    使用 try_cast 将溢出错误转换为 NULL

  • INTERVAL

    适用于:勾选“是” Databricks SQL 勾选“是” Databricks Runtime 11.2 及更高版本

    目标类型必须是精确数值

    给定一个 INTERVAL upper_unit TO lower_unit,以 lower_unit 的总数来测量结果。 如果 lower_unitSECOND,则秒的小数形式存储在小数点右侧。 所有其他间隔的结果始终为整数。

  • 布尔值

    如果 sourceExpr 为:

    • true:结果为 0。
    • false:结果为 1。
    • NULL:结果为 NULL

示例

> SELECT cast(NULL AS INT);
  NULL

> SELECT cast(5.6 AS INT);
  5

> SELECT cast(5.6 AS DECIMAL(2, 0));
  6

> SELECT cast(-5.6 AS INT);
  -5

> SELECT cast(-5.6 AS DECIMAL(2, 0));
  -6

> SELECT cast(128 AS TINYINT);
  Overflow

> SELECT cast(128 AS DECIMAL(2, 0));
  Overflow

> SELECT cast('123' AS INT);
  123

> SELECT cast('123.0' AS INT);
  Invalid format

> SELECT cast(TIMESTAMP'1970-01-01 00:00:01' AS LONG);
  1

> SELECT cast(TIMESTAMP'1970-01-01 00:00:00.000001' AS DOUBLE);
  1.0E-6

> SELECT cast(TIMESTAMP'2022-02-01 00:00:00' AS SMALLINT);
  error: overflow
> SELECT cast(true AS BOOLEAN);
  1

> SELECT cast(INTERVAL '1-2' YEAR TO MONTH AS INTEGER);
  14

> SELECT cast(INTERVAL '1:30.5' MINUTE TO SECOND AS DECIMAL(5, 2));
  90.50

STRING

如果 targetType字符串类型并且 sourceExpr 的类型为:

  • VOID

    结果是一个 NULL 字符串。

  • 精确数字

    结果是带有可选减号且不带前导零(小数点左侧的单个数字除外)的文本数字。 如果 targetTypeDECIMAL(p, s)s 大于 0,则添加小数点并将尾部零相加以得出标量。

  • 浮点二进制数

    如果绝对数小于 10,000,000 且大于或等于 0.001,则结果不使用科学记数法表示,小数点两侧至少包含一位数。

    否则,Azure Databricks 使用尾数,后接 E 和一个指数。 尾数包含一个可选的前导减号,后接小数点左侧的一位数,以及右侧大于零的最小位数。 指数包含可选的前导减号。

  • 日期

    如果年份介于公元前 9999 年和公元 9999 年之间,则结果分别为 -YYYY-MM-DDYYYY-MM-DD 格式的 dateString

    对于此范围之前或之后的年份,将必要的位数添加到年份组成部分,+ 用于表示“公元”。

  • 时间戳

    如果年份介于公元前 9999 年和公元 9999 年之间,则结果分别为 -YYYY-MM-DD hh:mm:ssYYYY-MM-DD hh:mm:ss 格式的 timestampString

    对于此范围之前或之后的年份,将必要的位数添加到年份组成部分,+ 用于表示“公元”。

    根据需要添加小数秒 .f...

  • year-month interval

    结果是间隔文本的最短表示形式。 如果间隔为负数,则在 interval-string 中嵌入符号。 对于小于 10 的单位,将省略前导零。

    典型的年月间隔字符串采用以下格式:

    • INTERVAL 'Y' YEAR
    • INTERVAL 'Y-M' YEAR TO MONTH
    • INTERVAL 'M' MONTH
  • day-time interval

    结果是间隔文本的最短表示形式。 如果间隔为负数,则在 interval-string 中嵌入符号。 对于小于 10 的单位,将省略前导零。

    典型的日期时间间隔字符串采用以下格式:

    • INTERVAL 'D' DAY
    • INTERVAL 'D h' DAY TO HOUR
    • INTERVAL 'D h:m' DAY TO MINUTE
    • INTERVAL 'D h:m:s' DAY TO SECOND
    • INTERVAL 'h' HOUR
    • INTERVAL 'h:m' HOUR TO MINUTE
    • INTERVAL 'm:s' MINUTE TO SECOND
    • INTERVAL 's' SECOND
  • 布尔值

    true 布尔值的结果是字符串文本 true;对于 false,它是字符串文本 false;对于 NULL,它是 NULL 字符串。

  • 二进制

    结果是解释为 UTF-8 字符序列的二进制 sourceExpr

    Azure Databricks 不验证 UTF-8 字符。 从 BINARYSTRING 的强制转换永远不会注入替代字符或引发错误。

  • 数组

    结果是逗号分隔的强制转换元素列表,括在方括号 [ ] 中。 每个逗号后接一个空格。 NULL 元素转换为文本 null

    Azure Databricks 不会用引号括住或以其他方式标记单个元素,这些元素本身可以包含括号或逗号。

  • 映射

    结果是逗号分隔的强制转换键值对列表,括在大括号 { } 中。 每个逗号后接一个空格。 每个键值对由 -> 分隔。 NULL 映射值转换为文本 null

    Azure Databricks 不会用引号括住或以其他方式标记单个键或值,这些键或值本身可以包含大括号、逗号或 ->

  • 结构

    结果是逗号分隔的强制转换字段值列表,括在大括号 { } 中。 每个逗号后接一个空格。 NULL 字段值转换为文本 null

    Azure Databricks 不会用引号括住或以其他方式标记单个字段值,这些值本身可以包含大括号或逗号。

示例

> SELECT cast(NULL AS STRING);
  NULL

> SELECT cast(-3Y AS STRING);
  -3

> SELECT cast(5::DECIMAL(10, 5) AS STRING);
  5.00000

> SELECT cast(12345678e-4 AS STRING);
  1234.5678

> SELECT cast(1e7 as string);
  1.0E7

> SELECT cast(1e6 as string);
  1000000.0

> SELECT cast(1e-4 as string);
  1.0E-4

> SELECT cast(1e-3 as string);
  0.001

> SELECT cast(12345678e7 AS STRING);
  1.2345678E14

> SELECT cast(DATE'1900-12-31' AS STRING);
  1900-12-31

-- Caesar no more
> SELECT cast(DATE'-0044-03-15' AS STRING);
  -0044-03-15

> SELECT cast(DATE'100000-12-31' AS STRING);
  +100000-12-31

> SELECT cast(current_timestamp() AS STRING);
  2022-04-02 22:29:09.783

> SELECT cast(INTERVAL -'13-02' YEAR TO MONTH AS STRING);
  INTERVAL '-13-2' YEAR TO MONTH

> SELECT cast(INTERVAL '12:04.9900' MINUTE TO SECOND AS STRING);
  INTERVAL '12:04.99' MINUTE TO SECOND

> SELECT cast(true AS STRING);
  true

> SELECT cast(false AS STRING);
  false

-- A bad UTF-8 string
> SELECT cast(x'33800033' AS STRING);
  3�3

> SELECT hex(cast(x'33800033' AS STRING));
  33800033

> SELECT cast(array('hello', NULL, 'world') AS STRING);
  [hello, null, world]

> SELECT cast(array('hello', 'wor, ld') AS STRING);
  [hello, wor, ld]

> SELECT cast(array() AS STRING);
  []

> SELECT cast(map('hello', 1, 'world', null) AS STRING);
  {hello -> 1, world -> null}

> SELECT cast(map('hello -> 1', DATE'2022-01-01') AS STRING);
  {hello -> 1 -> 2022-01-01}

> SELECT cast(map() AS STRING);
  {}

> SELECT cast(named_struct('a', 5, 'b', 6, 'c', NULL) AS STRING);
  {5, 6, null}

> SELECT cast(named_struct() AS STRING);
  {}

DATE

如果 targetType日期类型并且 sourceExpr 的类型为:

  • VOID

    结果为 NULL 日期。

  • 字符串

    sourceExpr 必须是有效的 dateString

    如果 sourceExpr 不是有效的 dateString,则 Azure Databricks 会返回错误。

    使用 try_cast 将无效数据错误转换为 NULL

  • 时间戳

    结果是时间戳 sourceExpr 的日期部分。

示例

> SELECT cast(NULL AS DATE);
  NULL

> SELECT cast('1900-10-01' AS DATE);
  1900-10-01

> SELECT cast('1900-10-01' AS DATE);
  1900-10-01

-- There is no February 30.
> SELECT cast('1900-02-30' AS DATE);
  Error

> SELECT cast(TIMESTAMP'1900-10-01 12:13:14' AS DATE);
  1900-10-01

TIMESTAMP

如果 targetType时间戳类型并且 sourceExpr 的类型为:

  • VOID

    结果为 NULL 日期。

  • 数字

    sourceExpr 读取为自 1970-01-01 00:00:00 UTC 以来经过的秒数。

    小于微秒的小数部分将被截断。

    如果值超出 TIMESTAMP 的范围,则引发溢出错误。

    使用 try_cast 将溢出错误转换为 NULL

  • 字符串

    sourceExpr 必须是有效的 timestampString

    如果 sourceExpr 不是有效的 timestampString,则 Azure Databricks 会返回错误。

    使用 try_cast 将无效数据错误转换为 NULL

  • 日期

    结果是 00:00:00 小时后所处的 sourceExpr 日期。

示例

> SELECT cast(NULL AS TIMESTAMP);
  NULL

> SET TIME ZONE '+00:00';
> SELECT cast(0.0 AS TIMESTAMP);
  1970-01-01 00:00:00

> SELECT cast(0.0000009 AS TIMESTAMP);
  1970-01-01 00:00:00

> SELECT cast(1e20 AS TIMESTAMP);
  Error: overflow

> SELECT cast('1900' AS TIMESTAMP);
  1900-01-01 00:00:00

> SELECT cast('1900-10-01 12:13:14' AS TIMESTAMP);
  1900-10-01 12:13:14

> SELECT cast('1900-02-30 12:13:14' AS TIMESTAMP);
  Error

> SELECT cast(DATE'1900-10-01' AS TIMESTAMP);
  1900-10-01 00:00:00

year-month interval

如果 targetType年月间隔并且 sourceExpr 的类型为:

示例

> SELECT cast(NULL AS INTERVAL YEAR);
  NULL

> SELECT cast('1-4' AS INTERVAL YEAR TO MONTH)::STRING;
  INTERVAL '1-4' YEAR TO MONTH

> SELECT cast('1' AS INTERVAL YEAR TO MONTH);
  error

> SELECT cast(INTERVAL '1-4' YEAR TO MONTH AS INTERVAL MONTH)::STRING;
  INTERVAL '16' MONTH

> SELECT cast(14 AS INTERVAL YEAR TO MONTH)::STRING;
  INTERVAL '1-2' YEAR TO MONTH

> SELECT cast(INTERVAL '1-11' YEAR TO MONTH AS INTERVAL YEAR)::STRING;
  INTERVAL '1' YEAR

day-time interval

如果 targetType日期时间间隔并且 sourceExpr 的类型为:

  • VOID

    结果是 NULL 日期时间间隔。

  • exact_numeric

    适用于:勾选“是” Databricks SQL 勾选“是” Databricks Runtime 11.2 及更高版本

    该数值解释为 targetTypedayTimeIntervalQualifier 的低单位数。 如果单位是 SECOND,任何小数将解释为秒的小数形式。

  • 字符串

    sourceExpr 必须是有效的 dayTimeIntervalString

    如果 sourceExpr 不是有效的 dayTimeIntervalString,则 Azure Databricks 会返回错误。

    使用 try_cast 将无效数据错误转换为 NULL

  • day-time interval

    如果 targetTypedayTimeIntervalQualifier 包含源类型 dayTimeIntervalQualifier 的最小单位,则值保持不变,但被重新解释以匹配目标类型。

    否则,将截断 sourceExpr 间隔以适合 targetType

> SELECT cast(NULL AS INTERVAL HOUR);
  NULL

> SELECT cast('1 4:23' AS INTERVAL DAY TO MINUTE)::STRING;
  INTERVAL '1 04:23' DAY TO MINUTE

> SELECT cast('1' AS INTERVAL DAY TO MINUTE);
  error

> SELECT cast(INTERVAL '1 4:23' DAY TO MINUTE AS INTERVAL MINUTE)::STRING;
  INTERVAL '1703' MINUTE

> SELECT cast(INTERVAL '1 4:23' DAY TO MINUTE AS INTERVAL HOUR)::STRING;
  INTERVAL '28' HOUR

> SELECT cast(125.3 AS INTERVAL MINUTE TO SECOND)::STRING;
  INTERVAL '2:5.3' MINUTE TO SECOND

BOOLEAN

如果 targetType布尔值并且 sourceExpr 的类型为:

  • VOID

    结果是 NULL 布尔值。

  • 数字

    如果 sourceExpr 为:

  • 字符串

    如果 sourcEexpr 是(不区分大小写):

    • 'T', 'TRUE', 'Y', 'YES', or '1':结果为 true
    • 'F', 'FALSE', 'N', 'NO', or '0':结果为 false
    • NULL:结果为 NULL

    否则,Azure Databricks 将为类型布尔值错误返回无效的输入语法。

    使用 try_cast 将无效数据错误转换为 NULL

示例

> SELECT cast(NULL AS BOOLEAN);
  NULL

> SELECT cast('T' AS BOOLEAN);
  true

> SELECT cast('True' AS BOOLEAN);
  true

> SELECT cast('1' AS BOOLEAN);
  true

> SELECT cast('0' AS BOOLEAN);
  false

> SELECT cast('n' AS BOOLEAN);
  false

> SELECT cast('on' AS BOOLEAN);
  error: invalid input syntax for type boolean

> SELECT cast(0 AS BOOLEAN);
  false

> SELECT cast(0.0E10 AS BOOLEAN);
  false

> SELECT cast(1 AS BOOLEAN);
  true

> SELECT cast(0.1 AS BOOLEAN);
  true

> SELECT cast('NaN'::FLOAT AS BOOLEAN);
  true

BINARY

如果 targetType二进制值并且 sourceExpr 的类型为:

  • VOID

    结果是 NULL 二进制值。

  • 字符串

    结果是 surceExpr 的 UTF-8 编码。

示例

> SELECT cast(NULL AS BINARY);
  NULL

> SELECT hex(cast('Spark SQL' AS BINARY));
  537061726B2053514C

> SELECT hex(cast('Oдesa' AS BINARY));
  4FD0B4657361

ARRAY

如果 targetTypeARRAY <> 并且 sourceExpr 的类型为:

  • VOID

    结果是 targeType 的 NULL 值。

  • ARRAY <sourceElementType>

    如果支持从 sourceElementTypetargetElementType 的强制转换,则结果是 ARRAY<targetElementType>,其中的所有元素已强制转换为 targetElementType

    如果不支持强制转换或无法强制转换任一元素,则 Azure Databricks 会引发错误。

    使用 try_cast 将无效数据或溢出错误转换为 NULL

示例

> SELECT cast(NULL AS ARRAY<INT>);
  NULL

> SELECT cast(array('t', 'f', NULL) AS ARRAY<BOOLEAN>);
  [true, false, NULL]

> SELECT cast(array('t', 'f', NULL) AS INTERVAL YEAR);
  error: cannot cast array<string> to interval year

> SELECT cast(array('t', 'f', 'o') AS ARRAY<BOOLEAN>);
  error: invalid input syntax for type boolean: o.

MAP

如果 targetTypeMAP <targetKeyType, targetValueType>并且 sourceExpr 的类型为:

  • VOID

    结果是 targetType 的 NULL 值。

  • MAP <sourceKeyType, sourceValueType>

    如果支持从 sourceKeyTypetargetKeyType 以及从 sourceValueTypetargetValueType 的强制转换,则结果是 MAP<targetKeyType, targetValueType>,其中的所有键已强制转换为 targetKeyType,所有值已强制转换为 targetValueType

    如果不支持强制转换或无法强制转换任一键或值,则 Azure Databricks 会引发错误。

    使用 try_cast 将无效数据或溢出错误转换为 NULL

示例

> SELECT cast(NULL AS MAP<STRING, INT>);
  NULL

> SELECT cast(map('10', 't', '15', 'f', '20', NULL) AS MAP<INT, BOOLEAN>);
  {10:true,15:false,20:null}

> SELECT cast(map('10', 't', '15', 'f', '20', NULL) AS MAP<INT, ARRAY<INT>>);
  error: cannot cast map<string,string> to map<int,array<int>>

> SELECT cast(map('10', 't', '15', 'f', '20', 'o') AS MAP<INT, BOOLEAN>);
  error: invalid input syntax for type boolean: o.

STRUCT

如果 targetTypeSTRUCT <[targetFieldName : targetFieldType [NOT NULL] [COMMENT str] [, …]] > 并且 sourceExpr 的类型为:

  • VOID

    结果是 targetType 的 NULL 值。

  • STRUCT < [sourceFieldName : sourceFieldType [NOT NULL] [COMMENT str] [, …]] >

    如果满足以下所有条件,则 sourceExpr 可以强制转换为 targetType

    • 源类型与目标具有相同数量的字段
    • 对于所有字段:sourceFieldTypeN 可以强制转换为 targetFieldTypeN
    • 对于所有字段值:源字段值 N 可以强制转换为 targetFieldTypeN,如果目标字段 N 标记为 NOT NULL,则值不为 null。

    sourceFieldName、源 NOT NULL 约束和源 COMMENT 不需要与 targetType 匹配,且将被忽略。

    如果不支持强制转换或无法强制转换任一键或值,则 Azure Databricks 会引发错误。

    使用 try_cast 将无效数据或溢出错误转换为 NULL

示例

> SELECT cast(NULL AS STRUCT<a:INT>);
  NULL

> SELECT cast(named_struct('a', 't', 'b', '1900') AS STRUCT<b:BOOLEAN, c:DATE NOT NULL COMMENT 'Hello'>);
  {"b":true,"c":1900-01-01}

> SELECT cast(named_struct('a', 't', 'b', NULL::DATE) AS STRUCT<b:BOOLEAN, c:DATE NOT NULL COMMENT 'Hello'>);
  error: cannot cast struct<a:string,b:date> to struct<b:boolean,c:date>

> SELECT cast(named_struct('a', 't', 'b', '1900') AS STRUCT<b:BOOLEAN, c:ARRAY<INT>>);
  error: cannot cast struct<a:string,b:string> to struct<b:boolean,c:array<int>>

> SELECT cast(named_struct('a', 't', 'b', 'hello') AS STRUCT<b:BOOLEAN, c:DATE>);
  error: Cannot cast hello to DateType