Skip to content

    • Beast Mode

    Beast Mode

    How can I get rid of extra characters in attributes

    Member
    in Beast Mode

    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.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

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

      SPLIT_PART(fieldname,'_',2)

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

    • Contributor

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

      1. SUBSTRING(SUBSTRING(`FIeld`, INSTR(`FIeld`, '_')+1), 1, INSTR(SUBSTRING(`FIeld`, INSTR(`FIeld`, '_')+1), '_')-1)
    • Coach
      Answer ✓

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

      SPLIT_PART(fieldname,'_',2)

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

      Here's a REGEXP version:

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

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Member

      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.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories