使用RLIKE不一致将Snowflake存储过程中的VARCHAR值的REGEX转换为DATE [英] REGEX conversion of VARCHAR value to DATE in Snowflake stored procedure using RLIKE not consistent

问题描述

我正在尝试通过Snowflake存储过程将日期格式混合的列-2017/12/10、2018-02-27、8/18/2017转换为YYYY-MM-DD格式。通过CALL语句执行时，它执行CASE语句的顺序似乎不一致。

表A：

CREATE TABLE TABLE_A
(
START_DATE VARCHAR,
END_DATE VARCHAR,
RECORDED_DATE VARCHAR);

INSERT INTO TABLE_A VALUES ('2021-11-09', '2021-11-09','2018/03/29');
INSERT INTO TABLE_A VALUES ('2021-11-09', '2021-11-09','2018-02-27');
INSERT INTO TABLE_A VALUES ('2021-11-09', '2021-11-09','8/18/2017');

存储过程：

CREATE OR REPLACE PROCEDURE LOAD_TABLE_B(LD VARCHAR)
RETURNS STRING 
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var insert_command =`INSERT INTO TABLE_B

SELECT START_DATE
,END_DATE
,CASE WHEN RECORDED_DATE RLIKE '\d{4}/\d{2}/\d{2}' THEN TO_DATE(RECORDED_DATE, 'YYYY/MM/DD')
ELSE TO_DATE(RECORDED_DATE)
END  AS RECORDED_DATE
,HASH(S.$1,S.$2,S.$3) AS CHECKSUM_HASH    
FROM TABLE_A  S;
`;

try {
    snowflake.execute({sqlText:insert_command});
    return "Success";   
    } 
catch (err)  {
    throw err;    
        }

$$ ;

CALL LOAD_TABLE_B(1);

错误消息：

Execution error in store procedure LOAD_TABLE_B: Date '2018/03/29' is not recognized At Snowflake.execute, line 18 position 11

推荐答案

，因为您正在存储过程中运行它。查询本身在执行之前有额外的一轮解析和字符转义。这意味着您需要额外的反斜杠。语法变得近乎愚蠢，但这正是您需要的。

var insert_command =`CREATE OR REPLACE TABLE TABLE_B AS
SELECT START_DATE
,END_DATE
,CASE WHEN RECORDED_DATE RLIKE '\\d{4}/\\d{2}/\\d{2}' THEN TO_DATE(RECORDED_DATE, 'YYYY/MM/DD')
ELSE TO_DATE(RECORDED_DATE)
END  AS RECORDED_DATE
,HASH(S.$1,S.$2,S.$3) AS CHECKSUM_HASH    
FROM TABLE_A  S;
`;

这篇关于使用RLIKE不一致将Snowflake存储过程中的VARCHAR值的REGEX转换为DATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！