How can I split 1 column into multiple columns

angelsinha
angelsinha Member
in Magic ETL

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

  • Gimli
    Gimli Domo Employee
    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

    **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"
  • Gimli
    Gimli Domo Employee
    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 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"

Answers

  • kshah008
    kshah008 Contributor

    Hi all,

     

    Can anybody help @angelsinha out?

    Thanks!

  • Gimli
    Gimli Domo Employee
    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

    **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"
  • angelsinha
    angelsinha Member

    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
    Gimli Domo Employee

    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"
  • Gimli
    Gimli Domo Employee
    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 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"
  • kshah008
    kshah008 Contributor

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

  • angelsinha
    angelsinha Member

    Yes MySQL has been enabled for our account.

     

    Thanks,
    Angel

  • jlazerus
    jlazerus Member

    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
    Mayur_Patil Member

    There is any solution for the same in ETL?

  • mhouston
    mhouston Contributor

    @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 Coach

    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

    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"

Categories