雪花词减法的IS_REAL算法

Question

我有一个名为newspaper_cd的列，可以是A129或FHID。我需要首先从右边提取三个数字，然后在雪花中添加一个条件表达式。问题是当前列是字符串，即使正确的3位数是一个数字，它仍然被视为字符串。

case when is_real(to_variant(right(newspaper_cd,3))) = True then 1 else 0
---  I got all 0s, because it is a string column

--- If I change it for a number thinking about the case like A129
to_number(right(newspaper_cd,3))
--- 'HID' is not recognized for 'FHID'

那我该怎么处理呢？谢谢。

Answer 1

使用数

SELECT newspaper_cd, TRY_TO_NUMBER(right(newspaper_cd,3)) IS NOT NULL
FROM tab

输出：

A129  True
FHID  False

Answer 2

您还可以将SUBSTR应用于具有varchar的列，如下所示

select
    CASE
        WHEN IS_REAL(TO_NUMBER(substr(newspaper_cd, -3, 3))) = TRUE THEN 1
        else 0
    end;