using regexp_replace for replacing special characters in snowflake

user_14052971 0
Viewed 5223

there are special characters in one of column and need to replace with space. i am using REGEXP_REPLACE, but throwing error as the columns is having collation as 'en-ci'

SQL COMMAND : select REGEXP_REPLACE(INPUT_STRING, '[+,()]', ' ',1,0) FROM TABLE;

below is the error: SQL compilation error: error line 1 at position 7 Function REGEXP_REPLACE does not support collation.

your suggessions may greatly helpful.

Thanks.

snowflake-cloud-data-platform
2 Answers

I confirmed the behavior that columns collated using en-ci don't work with REGEXP_REPLACE. I suspected and confirmed that JavaScript regex replacements in Snowflake UDFs will work.

I wrote a set of Snowflake UDFs to approximate the syntax of Snowflake regular expression functions as closely as possible using JavaScript's regular expressions. The point of that was to support lookaheads and lookbehinds, but the same functions will support this use case too.

You can get the UDFs here: https://github.com/GregPavlik/SnowflakeUDFs/blob/main/Regex.txt

You can get brief documentation here: https://github.com/GregPavlik/SnowflakeUDFs/blob/main/RegExp2_Functions.pdf

You can then run your statements by adding a "2" at the end of the name of a Snowflake regular expression function. It will then use the UDF that simulates that function's behavior but using JavaScript's regular expressions:

select REGEXP_REPLACE2(INPUT_STRING, '[+,()]', ' ',1,0) FROM TABLE;
user_127563810

You just have to remove the collation from the column by passing an empty string to the <collation_specification> parameter of the COLLATE function, just like this COLLATE(COLLATED_COLUMN, '')
This works for any regex related function in snowflake that doesn't support collation :)

In your case, you should be doing something like this:
select REGEXP_REPLACE(COLLATE(INPUT_STRING, ''), '[+,()]', ' ', 1, 0) FROM TABLE

P.S: This one was hard to figure out, greetings from Peru!

user_41544600
Related