Parsing Data Using Regex to Return Desired Values

npeirce1
npeirce1 Member
edited March 2023 in Datasets

I need to parse rows of data to extract a 3-4 letter code which is in all caps and create a new column containing those codes (RHL in this example)

Inv RHL - Earn/Loss

I can't use left/right functions because each account will have a different # of characters on each side of the code. Regex in replace text only allows you to return a constant value if the pattern is hit, I need it to return the solution of the regex formula. Can DOMO do this?

Answers

  • GrantSmith
    GrantSmith Coach

    Are there any other sort of identifiers on how to extract the code? Is it always [space]AAA[space] for the format? Would that ever appear multiple times in the string?

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

    Yes, there is an identifier I can use. I know how to write the formula to identify the codes, I'm wondering if Domo can pull out values from the middle of the rows and put them in a new column?

  • GrantSmith
    GrantSmith Coach

    Not exactly pull out a match but you can use REGEXP_REPLACE to fin the string and remove everything else from it.

    REGEXP_REPLACE(`value`, '^.*(MATCHCODE).*$', '$1')

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

Categories