Skip to content

    • Magic ETL

    Magic ETL

    How can I split 1 column into multiple columns

    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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Domo Employee
      Answer ✓

      Hello @angelsinha,

      We can use a SUBSTRING_INDEX() function in MySQL dataflows to accomplish this. 

      An example query would be: 

      1. 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"
    • 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

    • Contributor

      Hi all,

       

      Can anybody help @angelsinha out?

      Thanks!

    • Domo Employee
      Answer ✓

      Hello @angelsinha,

      We can use a SUBSTRING_INDEX() function in MySQL dataflows to accomplish this. 

      An example query would be: 

      1. 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"
    • 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

    • 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"
    • 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"
    • Contributor

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

    • Member

      Yes MySQL has been enabled for our account.

       

      Thanks,
      Angel

    • Member

      Here's a way to split each separation into a row, just in case anyone needs that in the future.

       

      1. 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
    • Member

      There is any solution for the same in ETL?

    • Contributor

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

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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories