Which Redshift SQL version / functions are supported?

user10926
user10926 Member
in Connectors

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.

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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:

    Screen Shot 2021-04-06 at 3.19.09 PM.png


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user10926
    user10926 Member

    Hey Grant,


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

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories