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

swagner
swagner Contributor
in Charting

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

89873-98u92-JLKJD@HUN-EMT34

OIUE-19837-LKJLILJ&18347-KLN89

Best Answer

  • GrantSmith
    GrantSmith Coach
    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}]}]}
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach
    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}]}]}
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

    @GrantSmith I knew you were going to suggest regex! 😂

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • swagner
    swagner Contributor

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

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • swagner
    swagner Contributor

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

  • MarkSnodgrass
    MarkSnodgrass Coach

    @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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • swagner
    swagner Contributor

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

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


    https://www.onyxreporting.com/blog/domo-parsing-json-in-a-mysql-56-dataflow


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

    @jaeW_at_Onyx Thanks!

Categories