Snowflake: replacing column values-CodePudding

I have a column that contains both numbers and alphanumeric characters in it. For number values in the column, I am just trying to replace them a different set of numbers. For alphanumeric columns, I am replacing them w/ different letters and numbers. Below are a few values:

select * from t1;

Below SQL is not working for some reason:

select col1,  
case   
when  regexp_like(col1,'^[A-Z] $')  
then   replace(replace(replace(replace(replace(replace(col1,'A','Z'),'B','Y'),'C','X'),'D','W'),'E','V'),'F','U')  
when try_to_number(col1) is not null  
then round(to_number(col1)*1.5)  
end as col1_replaced  
from t1;

what I could be doing wrong here?

Output I'm getting now:

COL1    COL1_REPLACED  
1234    1851  
AB145C  NULL  
678BC   NULL  
8765    13148  
3786CA  NULL

Desired output:

COL1    COL1_REPLACED  
1234    1851  
AB145C  ZY145X  
678BC   678YX  
8765    13148  
3786CA  3786XZ

CodePudding user response：

All branches of a CASE expression generally need to have the same type. Since the first branch is generating text, therefore the ELSE branch should also do the same. You may cast the ROUND expression to text here:

SELECT col1,
       CASE WHEN REGEXP_LIKE(col1, '^[A-Z] $')
            THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(col1, 'A', 'Z'), 'B', 'Y'), 'C', 'X'), 'D', 'W'), 'E', 'V'), 'F', 'U')
            WHEN TRY_TO_NUMBER(col1) IS NOT NULL
            THEN CAST(ROUND(TO_NUMBER(col1)*1.5) AS VARCHAR(15))
       END AS col1_replaced
FROM t1;