Cast a string column with empty strings to int
Hai,
I have e.g. the following string column containing either numbers or empty strings:
CREATE TABLE tst (s string); INSERT INTO tst VALUES ('1234'), (''), (‘4567');
I’d like to cast ’s' to an INT column. The following query works:
sql>SELECT CAST(s AS INT) FROM (SELECT CASE WHEN s='' THEN NULL ELSE s END AS s FROM tst) AS tmp; +------+ | L4 | +======+ | 1234 | | null | | 4567 | +------+ 3 tuples
But the following query doesn’t:
sql>SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed. sql>SELECT CASE WHEN s='' THEN CAST(NULL as INT) ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed.
This is probably because batcalc.int is applied on the whole “s” column as shown by the query plan:
sql>EXPLAIN SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; +--------------------------------------------------------------------------------------------------------------------+ | mal | +====================================================================================================================+ … | C_7:bat[:oid] := sql.tid(X_6:int, "sys":str, "tst":str); | | X_10:bat[:str] := sql.bind(X_6:int, "sys":str, "tst":str, "s":str, 0:int); | | X_19:bat[:str] := algebra.projection(C_7:bat[:oid], X_10:bat[:str]); | | X_21:bat[:bit] := batcalc.==(X_19:bat[:str], "":str); | | X_24:bat[:bit] := batcalc.isnil(X_21:bat[:bit]); | | X_30:bat[:bit] := batcalc.ifthenelse(X_24:bat[:bit], false:bit, X_21:bat[:bit]); | | X_35:bat[:int] := batcalc.int(X_19:bat[:str]); | | X_36:bat[:int] := batcalc.ifthenelse(X_30:bat[:bit], nil:int, X_35:bat[:int]); | …
Is my query wrong or the plan?
Thanks, Jennie
query:
SELECT s, cast(case s when '' then null else s end as int) si FROM tst;
works. so you need to do the cast on all values of the column, not some.
On 01-08-19 13:15, Ying Zhang wrote:
CREATE TABLE tst (s string); INSERT INTO tst VALUES ('1234'), (''), (‘4567');
I’d like to cast ’s' to an INT column. The following query works:
sql>SELECT CAST(s AS INT) FROM (SELECT CASE WHEN s='' THEN NULL ELSE s END AS s FROM tst) AS tmp; +------+ | L4 | +======+ | 1234 | | null | | 4567 | +------+ 3 tuples
But the following query doesn’t:
sql>SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed. sql>SELECT CASE WHEN s='' THEN CAST(NULL as INT) ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed.
This is probably because batcalc.int is applied on the whole “s” column as shown by the query plan:
sql>EXPLAIN SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; +--------------------------------------------------------------------------------------------------------------------+ | mal | +====================================================================================================================+ … | C_7:bat[:oid] := sql.tid(X_6:int, "sys":str, "tst":str); | | X_10:bat[:str] := sql.bind(X_6:int, "sys":str, "tst":str, "s":str, 0:int); | | X_19:bat[:str] := algebra.projection(C_7:bat[:oid], X_10:bat[:str]); | | X_21:bat[:bit] := batcalc.==(X_19:bat[:str], "":str); | | X_24:bat[:bit] := batcalc.isnil(X_21:bat[:bit]); | | X_30:bat[:bit] := batcalc.ifthenelse(X_24:bat[:bit], false:bit, X_21:bat[:bit]); | | X_35:bat[:int] := batcalc.int(X_19:bat[:str]); | | X_36:bat[:int] := batcalc.ifthenelse(X_30:bat[:bit], nil:int, X_35:bat[:int]); | …
Is my query wrong or the plan?
Thanks, Jennie
On 1 Aug 2019, at 13:28, martin van dinther martin.van.dinther@monetdbsolutions.com wrote:
query:
SELECT s, cast(case s when '' then null else s end as int) si FROM tst;
works. so you need to do the cast on all values of the column, not some.
Hai Martin,
Thanks! That works.
Jennie
On 01-08-19 13:15, Ying Zhang wrote:
CREATE TABLE tst (s string); INSERT INTO tst VALUES ('1234'), (''), (‘4567');
I’d like to cast ’s' to an INT column. The following query works:
sql>SELECT CAST(s AS INT) FROM (SELECT CASE WHEN s='' THEN NULL ELSE s END AS s FROM tst) AS tmp; +------+ | L4 | +======+ | 1234 | | null | | 4567 | +------+ 3 tuples
But the following query doesn’t:
sql>SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed. sql>SELECT CASE WHEN s='' THEN CAST(NULL as INT) ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed.
This is probably because batcalc.int is applied on the whole “s” column as shown by the query plan:
sql>EXPLAIN SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; +--------------------------------------------------------------------------------------------------------------------+ | mal | +====================================================================================================================+ … | C_7:bat[:oid] := sql.tid(X_6:int, "sys":str, "tst":str); | | X_10:bat[:str] := sql.bind(X_6:int, "sys":str, "tst":str, "s":str, 0:int); | | X_19:bat[:str] := algebra.projection(C_7:bat[:oid], X_10:bat[:str]); | | X_21:bat[:bit] := batcalc.==(X_19:bat[:str], "":str); | | X_24:bat[:bit] := batcalc.isnil(X_21:bat[:bit]); | | X_30:bat[:bit] := batcalc.ifthenelse(X_24:bat[:bit], false:bit, X_21:bat[:bit]); | | X_35:bat[:int] := batcalc.int(X_19:bat[:str]); | | X_36:bat[:int] := batcalc.ifthenelse(X_30:bat[:bit], nil:int, X_35:bat[:int]); | …
Is my query wrong or the plan?
Thanks, Jennie
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
martin van dinther -
Ying Zhang