Which Redshift SQL version / functions are supported?

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.
Answers
-
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:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Hey Grant,
I'm trying to sub string from a block of text using Regex not replace.
0 -
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!**0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 666 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive