topic REGEXP_REPLACE function giving different results in Greenplum and Snowflake in SAS Enterprise Guide

REGEXP_REPLACE function giving different results in Greenplum and Snowflake

naresh1 — Wed, 10 Mar 2021 06:41:12 GMT

Hi All

In SAS/ACCESS connect to Snowflake interface the function REGEXP_REPLACE is searching for exact pattern that we pass in the list of values to filter from DB.

Whereas SAS/ACCESS connect to Greenplum interface the same function REGEXP_REPLACE is searching for all pattern matches that we pass in the list of values to filter from DB.

COLUMN_NAME:

DD

MA COASTAL AREA

COASTAL AREA

GIS DD

GIS MET

GIS.MET

GIS/ROLLOUT

GIS/DD

Condition:

ex: where REGEXP_REPLACE(UPPER(COLUMN_NAME),'\s+$','') in ('COASTAL AREA','DD','MET','ROLLOUT')

Greenplum Output:

DD

MA COASTAL AREA

COASTAL AREA

GIS DD

GIS MET

GIS.MET

GIS/ROLLOUT

GIS/DD

Snowflake Output:

COASTAL AREA

How would i get exact above Greenplum result in snowflake ( without LIBNAME statement and Using explicit pass through facility only ).