Create Beast Mode Calculated Field from sub-string in Placement separated by multiple delimiters

jmach93
jmach93 Member
in Archive

Hi,

 

I need to select a substring from our Placement Names that returns the Creative Name that is nested within the Placement Name. The characters within the Placement Names and Creative Names vary, so I need MySQL code that works in BeastMode that concatenates all characters between the -3 underscore and the -2 underscore. Can someone please help with this?

 

example:

Placement Name

Partner_CampaignType_CreativeType_Targeting_CampaignName_OperatingSystem__CreativeSize_CreativeName_12345678_Pixel (1234567891234) 

 

Desired Result

CreativeName

Comments

  • ST_Superman
    ST_Superman Domo Employee

    I really wanted to learn how to do this with the regex function in the ETL dataflow.  However, I am not any good at regex so I gave up.

     

    Here is a solution for this with a MySQL select statement:1.png

     

     

    I will paste the code here so you can copy and paste...

     

    SELECT
    `Placement Name`
    ,SUBSTRING_INDEX(`part1`, '_',1) as `output`
    FROM
    (SELECT
    `Placement Name`
    ,SUBSTRING_INDEX(`Placement Name`,'_',-3) as `part1`
    FROM
    reg_exp_help) a

Categories