REGEXP_REPLACE

Description

These function searches a string for a regular expression pattern and replaces every occurrence of the pattern with the specified string. (SQL Language References REGEXP_REPLACE function).

Grammar Syntax

REGEXP_REPLACE( source_string, pattern [, replace_string [ , position [, parameters ] ] ] )

This function is partially supported by Snowflake.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE my_table (col1 varchar);
SELECT regexp_replace('the fox', 'FOX', 'quick brown fox', 1, 'ip');
SELECT 
regexp_replace(d, '[hidden]'),
regexp_replace(d, f)
FROM 
(SELECT 
regexp_replace('pASswd7','(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+') as d, 'passwd7' as f);
SELECT regexp_replace(col1, 'passwd7', '[hidden]', 1, 'ip') as rp from my_table;

Results

regexp_replace	regexp_replace	regexp_replace
the quick brown fox	pASsw7	pASsw7

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE my_table (col1 varchar)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "00/00/0000",  "domain": "test" }}';
SELECT
REGEXP_REPLACE('the fox', 'FOX', 'quick brown fox', 1, 0, 'i');
SELECT
REGEXP_REPLACE(d, '[hidden]'),
REGEXP_REPLACE(d,
                  --** SSC-FDM-0032 - PARAMETER 'regex_string' IS NOT A LITERAL VALUE, TRANSFORMATION COULD NOT BE FULLY APPLIED **
                  f)
FROM
(SELECT
REGEXP_REPLACE('pASswd7',
                         !!!RESOLVE EWI!!! /*** SSC-EWI-0009 - regexp_replace FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS ***/!!!
                         '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+') as d, 'passwd7' as f);
SELECT
REGEXP_REPLACE(col1, 'passwd7', '[hidden]', 1, 0, 'i') as rp from
my_table;

Results

REGEXP_REPLACE	RESULT
the quick brown fox	100048 (2201B): Invalid regular expression: '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', no argument for repetition operator: ?

Know Issues

1. This function includes a parameters argument that enables the user to interpret the pattern using the Perl Compatible Regular Expression (PCRE) dialect, represented by the p value, this is removed to avoid any issues when fixing the code.

Related EWIs

• SSC-EWI-0009: Regexp_Substr Function only supports POSIX regular expressions.

• SSC-FDM-0032: Parameter is not a literal value, transformation could not be fully applied

• SSC-FDM- PG0011: The use of the COLLATE column constraint has been disabled for this pattern-matching condition.