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

swagner

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

GrantSmith

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}]}]}

MarkSnodgrass

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

GrantSmith

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}]}]}

MarkSnodgrass

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

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

@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

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

MarkSnodgrass

@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

@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

@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

swagner

@jaeW_at_Onyx Thanks!