How can I split 1 column into multiple columns
Hi,
Is it possible to split 1 column to multiple columns by specifying the splitting character?
For Eg, I have column named Campaign and the values in the column follow a standard format like
'Campaign Date_Campaign Name_Campaign Type_Source_Language_Status'
Is there a way to split this column by '_' ?
Thanks,
Angel
Best Answers
-
Hello @angelsinha,
We can use a SUBSTRING_INDEX() function in MySQL dataflows to accomplish this.
An example query would be:SELECT
'Campaign Date_Campaign Name_Campaign Type_Source_Language_Status' 'Original Value'
,SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',1) '1'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',2),'_',-1) '2'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',3),'_',-1) '3'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',4),'_',-1) '4'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',5),'_',-1) '5'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',6),'_',-1) '6'
,SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',-1) 'LAST'
Here is a reference where you can learn more about this function: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"3 -
MySQL has been enabled for you. If you have further questions on how to accomplish something in MySQL please create a new Dojo post or email support@domo.com.
Thanks!**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Answers
-
0
-
Hello @angelsinha,
We can use a SUBSTRING_INDEX() function in MySQL dataflows to accomplish this.
An example query would be:SELECT
'Campaign Date_Campaign Name_Campaign Type_Source_Language_Status' 'Original Value'
,SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',1) '1'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',2),'_',-1) '2'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',3),'_',-1) '3'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',4),'_',-1) '4'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',5),'_',-1) '5'
,SUBSTRING_INDEX(SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',6),'_',-1) '6'
,SUBSTRING_INDEX('Campaign Date_Campaign Name_Campaign Type_Source_Language_Status','_',-1) 'LAST'
Here is a reference where you can learn more about this function: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"3 -
Hi @ilikenno,
Thanks for the reply. However, we do not have the SQL Dataflow feature enabled in our account. Is there any other alternative?
Thanks,
Angel
0 -
Hello Angel,
There are other ways of doing this. However mysql is far more effective than the other methods. The other methods can be very long and tedious if all your data is not the same length.
I saw your domobuzz for mysql dataflow access. I have put in the request to enable this for you. I will let you know as soon as it has been.
Thanks!**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
MySQL has been enabled for you. If you have further questions on how to accomplish something in MySQL please create a new Dojo post or email support@domo.com.
Thanks!**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
@angelsinha, tagging you to check out ilikenno's reply.
0 -
Yes MySQL has been enabled for our account.
Thanks,
Angel0 -
Here's a way to split each separation into a row, just in case anyone needs that in the future.
select
SUBSTRING_INDEX(SUBSTRING_INDEX(Column_Name, '_', numbers.n), '_', -1) name
from
(select 1 n union all
select 2 union all select 3 union all
select 4 union all select 5) numbers INNER JOIN tablename
on CHAR_LENGTH(Column_Name)
-CHAR_LENGTH(REPLACE(Column_Name, '_', ''))>=numbers.n-10 -
There is any solution for the same in ETL?
0 -
@Mayur_Patil there is a split column tile in Magic ETL that allows you to specify the delimiter to split on.
0 -
if you don't know how many _ are in your string, a cleaner method than using the split column tile might be to:
1) count the number of _ in your data (length(string) - length(replace(string, "_", ''))
2) duplicate the row <number_of_occurences>
3) keep everything between the n and n-1_th version of the _.
you can do this in MySQL or Magic 2.0
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive