SQL/Beast Mode to extract substrings and output to multiple new columns
I have a column in my data with strings that look like this:
Clusters affected: |
|Finance, Services & Innovation| |Planning & Environment| |Transport & Infrastructure| |
|Finance, Services & Innovation| |Planning & Environment| |
|Finance, Services & Innovation| |Health| |Industry| |Planning & Environment| |Premier & Cabinet| |
What I would like to do is extract the substring between each "| |" delimiter and save the output to a new column. Each row has a different number of entries in the string. The optimal output for the first row would look like:
First Cluster | Second Cluster | Third Cluster |
Finance, Services & Innovation | Planning & Environment | Transport & Infrastructure |
Greatly appreciative for any solutions or guidance!
0
Comments
-
Have you tried using the Magic ETL "Split Column" tile?
You should be able to delimit the column by "| |" and then do some more clean up for the first and last column
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
This is how I would do this in MySQL:
Transform 1:
SELECT
`Clusters affected:`
,substring_index(`Clusters affected:`,'| |',1) as `First Cluster`
,substring_index(substring_index(`Clusters affected:`,'| |',2),'| |',-1) as `Second Cluster`
,substring_index(substring_index(`Clusters affected:`,'| |',3),'| |',-1) as `Third Cluster`
,substring_index(substring_index(`Clusters affected:`,'| |',4),'| |',-1) as `Fourth Cluster`
,substring_index(substring_index(`Clusters affected:`,'| |',5),'| |',-1) as `Fifth Cluster`
,substring_index(substring_index(`Clusters affected:`,'| |',6),'| |',-1) as `Sixth Cluster`
FROM `dojo_jjk`Transform 2:
SELECT
`Clusters affected:`
,trim(BOTH '|' FROM `First Cluster`) as `First Cluster`
,trim(BOTH '|' FROM `Second Cluster`) as `Second Cluster`
,trim(BOTH '|' FROM `Third Cluster`) as `Third Cluster`
,trim(BOTH '|' FROM `Fourth Cluster`) as `Fourth Cluster`
,trim(BOTH '|' FROM `Fifth Cluster`) as `Fifth Cluster`
,trim(BOTH '|' FROM `Sixth Cluster`) as `Sixth Cluster`
FROM `transform_1`Transform 3:
SELECT
`Clusters affected:`
,`First Cluster`
,case when `Second Cluster` = `First Cluster` then null else `Second Cluster` end as `Second Cluster`
,case when `Third Cluster` = `Second Cluster` then null else `Third Cluster` end as `Third Cluster`
,case when `Fourth Cluster` = `Third Cluster` then null else `Fourth Cluster` end as `Fourth Cluster`
,case when `Fifth Cluster` = `Fourth Cluster` then null else `Fifth Cluster` end as `Fifth Cluster`
,case when `Sixth Cluster` = `Fifth Cluster` then null else `Sixth Cluster` end as `Sixth Cluster`
FROM `transform_2`This will give you a dataset like this:
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive