How can I split 1 column into multiple columns

angelsinha

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

Gimli

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

Gimli

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!

kshah008

Hi all,

 

Can anybody help @angelsinha out?

Thanks!

Gimli

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

angelsinha

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

Gimli

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!

Gimli

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!

kshah008

@angelsinha, tagging you to check out ilikenno's reply. 

angelsinha

Yes MySQL has been enabled for our account.

 

Thanks,
Angel

jlazerus

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-1

Mayur_Patil

There is any solution for the same in ETL?

mhouston

@Mayur_Patil there is a split column tile in Magic ETL that allows you to specify the delimiter to split on.

jaeW_at_Onyx

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 _.

https://www.youtube.com/watch?v=oYcpYE7DiV4

you can do this in MySQL or Magic 2.0