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
-
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!**2
Answers
-
@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.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
@GrantSmith I knew you were going to suggest regex! π
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
@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.
0 -
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!**3 -
@GrantSmith that is incredible! THANK YOU!!!!
0 -
@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
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.4 -
@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.
0 -
@swagner , here's the writeup for doing it in MySQL before Domo got clever and released the Formulas tile. Complete with video explanation :P
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"1 -
@jaeW_at_Onyx Thanks!
0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K ζ₯ζ¬ζ―ι¨
- 4 ιε ΄-ζ₯ζ¬ζ―ι¨γΈγγγγ
- 26 γη₯γγ
- 64 Kowaza
- 299 δ»²ιγ«ηΈθ«
- 653 γ²γγγε ±ζ