Beast Mode Approach to Eliminate Field Character

afieweger · 2023-10-18T19:12:37+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Beast Mode

Accepted answers

MichelleH

@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

@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`,'@')))),'>','')

All comments

MichelleH

@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

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

MarkSnodgrass

@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`,'@')))),'>','')

MichelleH

Good catch @MarkSnodgrass!

afieweger

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

pstrauss

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"