CAST_INVALID_INPUT錯誤類別
類型 <sourceType> 的值 <expression> 無法轉換成 <targetType>，因為格式不正確。
根據語法更正值，或變更其目標類型。
使用 try_cast 來容許格式不正確的輸入，並改為傳回NULL。
如有必要，將 set<ansiConfig> 設為「false」以略過此錯誤。
Parameters
-
表達式：需要被轉換成
targettype -
sourceType：
expression的數據類型。 - targetType：轉換作業的目標類型。
- ansiConfig：要改變 ANSI 模式的組態設定。
解釋
expression 因為下列其中一個原因而無法轉換成 targetType：
-
expression對於此型別的範圍而言太大。 例如，無法將數位1000轉換成TINYINT，因為該定義域的範圍從-128到+127。 -
expression包含不屬於型別的字元。 例如，a無法轉換成任何數值類型。 -
expression的格式化方式使轉型操作無法解析。 例如，1.0和1e1無法轉換成任何整數數值類型。
Azure Databricks 可能隱含地進行了類型轉換，而沒有明確指定。
此錯誤所提供的內容資訊會隔離發生錯誤的物件和表達式。
如需定義域和接受的常值格式，請參閱
緩解
此錯誤的緩解取決於其原因：
是否預期
value符合指定typeName的網域和格式？確認輸入產生值並更正數據來源。
投射的目標太窄嗎？
例如，藉由將類型從
DATE移至TIMESTAMP、從INT到BIGINT或移至DOUBLE，來擴大範圍。格式
value不正確嗎？請考慮使用：
這些函式可讓您指定各種不同的格式。
轉換包含小數點的數字文字（例如
1.0）或科學記數法（例如1e0）時，請考慮先將其轉型為DECIMAL或DOUBLE，然後再轉換為精確的數值。是否預期會有不正確的數據 values，並應透過產生 NULL 來容忍？
變更表示式使用或插入 try_cast（value AS typeName）。 此函式在沒有符合類型的
value的情況下，會傳回NULL。如果您無法變更表達式，作為最後手段，您可以使用
ansiConfig暫時停用 ANSI 模式。
例子
-- A view with a cast and string literals outside the domain of the target type
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT CAST(a AS SMALLINT) FROM VALUES('100'), ('50000') AS t(a);
> SELECT c1 FROM v;
[CAST_INVALID_INPUT] The value '50000' of the type "STRING" cannot be cast to "SMALLINT" because it is malformed.
Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead.
If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL of VIEW v(line 1, position 8) ==
SELECT CAST(a AS SMALLINT) FROM VALUES('100'), ('50000') A...
^^^^^^^^^^^^^^^^^^^
-- Widen the target type to match the domain of the input
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT cast(a AS INTEGER) FROM VALUES('100'), ('50000') AS t(a);
> SELECT c1 FROM v;
100
50000
-- The input data format does not match the target type
> SELECT cast(a AS INTEGER) FROM VALUES('1.0'), ('1e0') AS t(a);
[CAST_INVALID_INPUT] The value '1.0' of the type "STRING" cannot be cast to "INT" because it is malformed.
Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead.
If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT cast(a AS INTEGER) FROM VALUES('1.0'), ('1e0') AS ...
^^^^^^^^^^^^^^^^^^
-- Adjust the target type to the match the format if the format is indicative of the domain.
> SELECT cast(a AS DOUBLE) FROM VALUES('1.0'), ('1e0') AS t(a);
1.0
1.0
-- ALternatively double cast to preserver the target type
> SELECT cast(cast(a AS DOUBLE) AS INTEGER) FROM VALUES('1.0'), ('1e0') AS t(a);
1
1
-- The format of the numeric input contains display artifacts
> SELECT cast(a AS DECIMAL(10, 3)) FROM VALUES('12,345.30-'), ('12+') AS t(a);
[CAST_INVALID_INPUT] The value '12,345.30-' of the type "STRING" cannot be cast to "DECIMAL(10,3)" because it is malformed.
Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead.
If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT cast(a AS DECIMAL(10, 3)) FROM VALUES('$<123,45.30>'), ('...
^^^^^^^^^^^^^^^^^^^^^^^^^
-- Use to_number() to parse formatted values
> SELECT to_number(a, '9,999,999.999S') FROM VALUES('123,45.30-'), ('12+') AS t(a);
-12345.300
12.000
-- The format of a date input does not match the default format
> SELECT cast(geburtsdatum AS DATE) FROM VALUES('6.6.2000'), ('31.10.1970') AS t(geburtsdatum);
[CAST_INVALID_INPUT] The value '6.6.2000' of the type "STRING" cannot be cast to "DATE" because it is malformed.
Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead.
If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT cast(geburtsdatum AS DATE) FROM VALUES('6.6.2000'), ('31.1...
^^^^^^^^^^^^^^^^^^^^^^^^^^
-- Use to_date to parse the correct input format for a date
> SELECT to_date(geburtsdatum, 'dd.MM.yyyy') FROM VALUES('6.6.2000'), ('31.10.1970') AS t(geburtsdatum);
2000-06-06
1970-10-31
-- The type resolution of Databricks did not derive a sufficiently wide type, failing an implicit cast
> SELECT 12 * monthly AS yearly FROM VALUES ('1200'), ('1520.56') AS t(monthly);
[CAST_INVALID_INPUT] The value '1520.56' of the type "STRING" cannot be cast to "BIGINT" because it is malformed.
Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead.
If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT 12 * monthly AS yearly FROM VALUES ('1200'),...
^^^^^^^^^^^^
-- Explicitly declare the expected type
> SELECT 12 * cast(monthly AS DECIMAL(8, 2)) AS yearly FROM VALUES ('1200'), ('1520.56') AS t(monthly);
14400.00
18246.72
-- The input data is occasionally expected to incorrect
> SELECT cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer not to say') AS t(salary);
[CAST_INVALID_INPUT] The value 'prefer not to say' of the type "STRING" cannot be cast to "DECIMAL(9,2)" because it is malformed.
Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead.
If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer ...
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- Use try_cast to tolerate incorrect input
> SELECT try_cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer not to say') AS t(salary);
30000.00
NULL
-- In Databricks SQL temporarily disable ANSI mode to tolerate incorrect input.
> SET ANSI_MODE = false;
> SELECT cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer not to say') AS t(salary);
30000.00
NULL
> SET ANSI_MODE = true;
-- In Databricks Runtime temporarily disable ANSI mode to tolerate incorrect input.
> SET spark.sql.ansi.enabled = false;
> SELECT cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer not to say') AS t(salary);
30000.00
NULL
> SET spark.sql.ansi.enabled = true;