Parsing text with common delimiters
So I've now had a chance to reuse this solution about three times and that tells me that it is common enough that I should share it. The principle here is that you have a piece of text with a good naming convention that is delimited with a common character. For example, "Name_Date_Product_Channel" which is seperated by underscores.
This piece of SQL code I'm including below will allow you to parse through the text and pull individual elements out as columns you name. You'll edit the elements in orange, green, and blue below.
Select `your_column_here`,
-- Parse Column 1 through 4.
Case WHEN (LENGTH(`your_column_here`) - LENGTH(REPLACE(`your_column_here`, '_', ''))) BETWEEN 1 AND 4
THEN REPLACE(LOWER(SUBSTRING_INDEX(`your_column_here`,'_',1)),' ','')
ELSE NULL
END as 'Name',
CASE
WHEN (LENGTH(`your_column_here`) - LENGTH(REPLACE(`your_column_here`, '_', ''))) BETWEEN 1 AND 4
THEN REPLACE(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(`your_column_here`,'_',2),'_',-1)),' ','')
ELSE NULL
END as 'Date',
CASE
WHEN (LENGTH(`your_column_here`) - LENGTH(REPLACE(`your_column_here`, '_', ''))) BETWEEN 2 AND 4
THEN REPLACE(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(`your_column_here`,'_',3),'_',-1)),' ','')
ELSE NULL
END as 'Product',
CASE
WHEN (LENGTH(`your_column_here`) - LENGTH(REPLACE(`your_column_here`, '_', ''))) BETWEEN 3 AND 4
THEN REPLACE(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(`your_column_here`,'_',4),'_',-1)),' ','')
ELSE NULL
END as 'Channel'
FROM your_table_here
The case statments above can be extended to as many parsings as you need. If no match is found (in otherwords a null occurs in your data) it will just have a blank field.
I hope you find this as helpful as I have found it recently. If you use it and like it please upvote this entry!
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 656 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 811 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 80 App Studio
- 45 Variables
- 771 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 77 Workflows
- 23 Code Engine
- 36 AI and Machine Learning
- 19 AI Chat
- AI Playground
- AI Projects and Models
- 17 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive