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

  • ilikenno
    ilikenno

    domo

    💎

    Accepted Answer

    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'

    2016-08-22_2032.png
    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

  • ilikenno
    ilikenno

    domo

    💎

    Accepted Answer

    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 [email protected] 

    Thanks!

Answers

  • Hi all,

     

    Can anybody help @angelsinha out?

    Thanks!

  • 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

  • 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!

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

  • Yes MySQL has been enabled for our account.

     

    Thanks,
    Angel

  • 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
  • There is any solution for the same in ETL?

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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