雪花云数据平台需要帮助将字母数字列值转换为NULL

Question

我需要帮助将字母数字值从Col_A替换为NULL

SELECT distinct o_id,
CASE when REGEXP_COUNT(Col_A,'^[0-9]+$')=1 then null else Col_A end 
as Col_1
FROM db_name.schema_name.table_name
WHERE date_ordered  = '2022-02-03'

当我运行下面的查询时，我得到O_ID 的一个NULL值和一个NOT NULL值

select distinct o_id,Col_A from db_name.schema_name.table_005
WHERE o_id='3351456'
AND date_ordered= '2022-02-03'

O_ID	COL_A
93261	空
93261	ABCD12345

添加HAVING子句在聚合阶段(DISTINCT(之后运行

SELECT DISTINCT o_id,
CASE when REGEXP_COUNT(Col_A,'^[0-9]+$')=1 then null else Col_A END as Col_1
FROM table_005
WHERE date_ordered  = '2022-02-03'
HAVING Col_1 IS NOT NULL;    

给你预期的结果。

O_ID	COL_1
93261	ABCD12345