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
- 10.5K All Categories
- 7 Connect
- 917 Connectors
- 250 Workbench
- 465 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 173 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive