Beast Mode: Extract a string from a column based on a custom character is starting from right

I have some data we are pulling in that has a column full of text. The very last part of that string is a part number (variable length) that is always preceded by a dash. I'm wanting to split the column from the right at that dash. How would you do this with Beast Mode?

Examples: (the part number is shown in bold)

123-8473/JHEW-JJDJJD-SQDQO120

[email protected]EMT34

OIUE-19837-LKJLILJ&18347-KLN89

Best Answer

  • GrantSmith
    GrantSmith Indiana 🟀
    Accepted Answer

    Hi @swagner

    Alternatively you can utilize a regular expression in a Magic ETL Replace Text tile (not exactly what you asked for but wanted to share just in case you wanted to go this route):

    ^.*-([^-]+)$
    

    This will handle any number of dashes in your string.

    Copy and paste this into your Magic ETL:

    {"contentType":"domo/dataflow-actions","data":[{"name":"Replace Text","id":"d7a9e1bd-ebbb-4b13-ad2b-75fd0e98fa1b","type":"ReplaceString","gui":{"x":210,"y":408},"dependsOn":["83116b2f-90d1-4b7b-ad6b-83213ce84fa4"],"removeByDefault":false,"notes":[],"fields":[{"inStreamName":"Part","useRegex":true,"replaceString":"^.*-([^-]+)$","replaceByString":"$1","wholeWord":false,"caseSensitive":false}]}]}
    
    

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon πŸ”΅

    @swagner Assuming you have always 3 dashes in there (as shown in your examples) you can use SPLIT_PART to get the string after the 3rd dash, which is the 4th part.

    SPLIT_PART(`String`,'-',4)

    Hope this helps.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon πŸ”΅

    @GrantSmith I knew you were going to suggest regex! πŸ˜‚

  • swagner
    swagner 🟒

    @MarkSnodgrass thanks for the reply, unfortunately the dash appears a variable number of times (not just 3). I'll definitely keep that in mind for future. @GrantSmith not sure I understand, but will dig in now to wrap my head around it.

  • GrantSmith
    GrantSmith Indiana 🟀

    @swagner

    Since beast modes don't support regular expressions you'll need to do it within an ETL. The JSON code I posted above will give you the starting point for a Replace Text tile in a Magic ETL 2.0 dataflow. You can copy that second code block and then just paste it when in the ETL it'll put that tile in for you with the regex formula already applied.

    The regular expression is simply stating "Give me everything after the last - that isn't a dash" if that help clarifies things.

  • swagner
    swagner 🟒

    @GrantSmith that is incredible! THANK YOU!!!!

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon πŸ”΅

    @swagner great use of regex by @GrantSmith as usual.

    Just to see if I could, I came up with a way to do it in Beast Mode:

    SPLIT_PART(`String`,'-',LENGTH(`String`) - LENGTH(REPLACE(`String`,'-','')) + 1)

    To dynamically determine the number of dashes in the string, you can utilize the LENGTH and REPLACE functions. I am taking the length of the field (string) and then subtracting it from the length of the field after I remove all the dashes. This tells me how many dashes are in the string. I add 1 to the result so that I get the part after the last dash to use for the SPLIT_PART function

  • swagner
    swagner 🟒

    @MarkSnodgrass that's awesome! Much easier for me to "decode" and use for other things later. Thanks for providing that soluion as well. Hopefully this will help others in the future as well.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    @swagner , here's the writeup for doing it in MySQL before Domo got clever and released the Formulas tile. Complete with video explanation :P



  • swagner
    swagner 🟒

    @jaeW_at_Onyx Thanks!

Sign In or Register to comment.