Beast Mode Approach to Eliminate Field Character

afieweger
afieweger Member
in Beast Mode

I have a data set of email addresses and i've used a beast mode calc to create a new field for the domains.

RIGHT(Sent Email,((LENGTH(Sent Email))-(INSTR(Sent Email,'@'))))

The issue I'm having is that some of the email values also include the bracketed name of the user so some of the results are coming back looking like this:

hotmail.com>

gmail.com>

Is there an additional beast mode calc i can do against this new field I've created to eliminate that character when it exists. When it is there, its always the last character.

I'm non-tech, have zip experience in ETL and have my available dev team members focused on outward (client) facing priorities. My hope is there is a beast mode approach as I'm gaining familiarity there.

Thanks so much.

Tagged:

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @afieweger If it is always the same character that is showing up, then you can wrap your entire statement in a REPLACE function to remove all instances of the '>' character like this:

    REPLACE(RIGHT(`Sent Email`,((LENGTH(`Sent Email`))-(INSTR(`Sent Email`,'@')))),'<','')

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @MichelleH has what I would have suggested, but she mistyped the character you are looking for. :)

    Should be:

    REPLACE(RIGHT(`Sent Email`,((LENGTH(`Sent Email`))-(INSTR(`Sent Email`,'@')))),'>','')

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

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @afieweger If it is always the same character that is showing up, then you can wrap your entire statement in a REPLACE function to remove all instances of the '>' character like this:

    REPLACE(RIGHT(`Sent Email`,((LENGTH(`Sent Email`))-(INSTR(`Sent Email`,'@')))),'<','')

  • pauljames
    pauljames Contributor

    LEFT(new field ,LENGTH(new field)-1)

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @MichelleH has what I would have suggested, but she mistyped the character you are looking for. :)

    Should be:

    REPLACE(RIGHT(`Sent Email`,((LENGTH(`Sent Email`))-(INSTR(`Sent Email`,'@')))),'>','')

    **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.
  • MichelleH
    MichelleH Coach

    Good catch @MarkSnodgrass!

  • afieweger
    afieweger Member

    You guys rock. Thanks all for the help. Working perfectly!

  • pstrauss
    pstrauss Member

    Can you guys think of a way to do the same, but remove the last part of a string if it ends with specific characters?

    I'm concatenating a bunch of values into a comma-separated list in a column but want to eliminate the trailing comma. i.e.:

    "item1, item2, item3, item4," → "item1, item2, item3, item4"

Categories