Which Redshift SQL version / functions are supported?

user10926

Hey ya'll,

I'm trying to write query to do a Regex sub string on some text. Beast Mode and Magic ETL have Regex but not Regex sub string. MySQL has the capability but Domo is on version 5.x~. My last option was Redshift SQL.

My query:

SELECT SUBSTRING_REGEX("Description", '.*(Red).*') as color
from "clothing_db"

Error:

The database reported a syntax error: [Amazon](500310) Invalid operation: function substring_regex(character varying, "unknown") does not exist;

The closest function I found in the AWS Redshift documentation to Domo was REGEXP_SUBSTR.

Syntax:

REGEXP_SUBSTR ( source_string, pattern [, position [, occurrence [, parameters ] ] ] )

Can someone point me to some documenation on SUBSTRING_REGEX? Even better if anyone has found a solution for sub string extract with pattern they can share.

GrantSmith

Hi @user10926

Have you tried using the replace text tile in Magic ETL 2.0?

You can find the entire pattern and then just replace it with your match group number like below:

user10926

Hey Grant,

I'm trying to sub string from a block of text using Regex not replace.

GrantSmith

Hi @user10926

What you can do is make a copy of your column into a new column and then do the replace on your new column to do the substring.