How can I get rid of extra characters in attributes

levi_kim

Hi all,

I have a field that lists carriers in an odd fashion such as below:

000001_BOSMAN_T_GND

000001_UPS2_A_F-EX

000001_UPS2_A_LTL

I'd like to get rid of the unnecessary characters at the beginning and the end so it looks like this:

BOSMAN

UPS2

Is there a Beastmode function or calculation that can help me do that? Would like to avoid Magic ETL if possible but open to doing that if necessary.

MarkSnodgrass

If it is always between the first and second underscore, you can do this:

SPLIT_PART(fieldname,'_',2)

Sean_Tully

Try something like this, replacing the field with your actual field name:

SUBSTRING(SUBSTRING(`FIeld`, INSTR(`FIeld`, '_')+1), 1, INSTR(SUBSTRING(`FIeld`, INSTR(`FIeld`, '_')+1), '_')-1)

MarkSnodgrass

If it is always between the first and second underscore, you can do this:

SPLIT_PART(fieldname,'_',2)

GrantSmith

Here's a REGEXP version:

REGEXP_REPLACE(`field`, '^\d+-([^_]+).*$', '$1')

But as @MarkSnodgrass mentioned if your format is consistent where values are after the first underscore with a following underscore then SPLIT_PART is an easier option.

The REGEX version will handle cases where there's no following underscore

levi_kim

Thank you @Sean_Tully @MarkSnodgrass @GrantSmith !

Your answers are very insightful and helpful. I will definitely add them to my toolkit. I ended up using Mark's SPLIT_PART function as that was easiest and fit my criteria.