Try this:

Create or replace procedure insert_into_staged1()
RETURNS STRING NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
var sql_cmd = `
Insert into staging_table select xyz as xyz, abc as old_abc, 
case 
when rx_code in ('1234','5432') and trim(split_part(upper(abc),'*',1)) in  ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1))
when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) then 
             coalesce(replace(abc,'\"','\'''),'') end as abc_new from table2`;
           
 var sql = snowflake.createStatement({sqlText: sql_cmd});
 var result = sql.execute();
 return 'success';
 $$;

I tried running your SQL statement and found that the THEN was missing in one of the case.

The corrected function added.
    --ORiginal 
    select xyz as xyz, abc as old_abc, case when 
                   rx_code in ('1234','5432') and trim(split_part(upper(abc),'*',1)) in 
                     ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1))
                when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) else 
                 coalesce(replace(abc,'\"','\''),'') end as abc_new from table2
    
    
    --Corrected 
    select xyz as xyz, abc as old_abc, 
    case when rx_code in ('1234','5432') and trim(split_part(upper(abc),'*',1)) in ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1))
    when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) then 'Correction added'
    else coalesce(replace(abc,'\"','\''),'') end as abc_new from table2;



create table staging_table AS
select xyz as xyz, abc as old_abc
,case when rx_code in ('1234','5432') and  trim(split_part(upper(abc),'*',1)) in ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1))
when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) then 'missing then'
else coalesce(replace(abc,'\"','\''),'') end as abc_new 
from table2;

create or replace table  table2 (rx_code varchar2(100), split_part varchar2(100), xyz varchar2(120), abc varchar2(100));

select
    column1,
    case
        when column1=1 then 'one'
        when column1=2 then 'two'
        else 'other'
    end as result
from (values(1),(2),(3)) v;

Create or replace procedure insert_into_staged1()
RETURNS STRING NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
var sql_cmd = `select xyz as xyz, abc as old_abc
,case when rx_code in ('1234','5432') and  trim(split_part(upper(abc),'*',1)) in ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1))
when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) then 'missing then'
else coalesce(replace(abc,'\\"','\\''),'') end as abc_new 
from table2`
 var sql = snowflake.createStatement({sqlText: sql_cmd});
 var result = sql.execute();
 return 'success';
 $$;      
 
 
 call insert_into_staged1();