SQL to replace all occurrences from table using REGEX

I would like to know how I can replace all occurrences (from any column in a table) of \\\\N with an empty string. I think I should use the REGEX_REPLACE function, but I've only been able to see examples of it used on one column inside Snowflake.

REGEXP_REPLACE( <subject> , <pattern> [ , <replacement> , <position> , <occurrence> , <parameters> ] )

CodePudding user response：

You can generate the SQL to operate on each column using the 'show columns' then build a set up SQL statements using the lastqueryID

show columns in table mytable;
select 'update mytable set ' || "column_name" || ' = replace(' || "column_name" || ',''\\\\\\\\N'','''',);'  from TABLE(RESULT_SCAN(LAST_QUERY_ID()));

CodePudding user response：

What you're looking for is not possible natively in SQL. You could do

update your_table
set col1=replace(col1,'\\\\N',''),
    col2=replace(col2,'\\\\N',''),
    col3=replace(col3,'\\\\N',''),
    ....

I personally prefer the following because I can run the select portion to take a look at my output before making any changes

create or replace table your_table as

select top 0 * --to avoid having to write column names in subsequent select
from your_table
union all
select replace(col1,'\\\\N',''),
       replace(col2,'\\\\N',''),
       replace(col3,'\\\\N',''),
       ...
from your_table