Extracting digits from the String

zuchu
zuchu Member
in Magic ETL

Hello,

I'm trying to create a beast mode that would allow me to extract 5 digits that are always coming AFTER words 'Purchase Order'. My issue with it, there is a variable number of digits before depending on the row of data. As an example string might looks like:

ABCDSFF Purchase Order 12345 duewobfd

ADS Purchase Order 12345 1235315132

Every time I want to exclude only the '12345' from the string, and the only other rule is that those number are always after phrase 'Purchase Order'

Is there a way to extract those 5 digits?

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use a formula tile and a regexp_replace function to strip out the numbers.

    REGEXP_REPLACE(`field`, '^.Purchase Order (\d+).$', '$1')

    This will get all numbers after Purchase Order until it doesn't see a number so this would extract 12345 and not ' 12345 1235315132' from your second example as there is a space after 12345'

    If you want to explicitly pull 5 digits then you can replace the + with {5}

    REGEXP_REPLACE(`field`, '^.Purchase Order (\d{5}).$', '$1')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use a formula tile and a regexp_replace function to strip out the numbers.

    REGEXP_REPLACE(`field`, '^.Purchase Order (\d+).$', '$1')

    This will get all numbers after Purchase Order until it doesn't see a number so this would extract 12345 and not ' 12345 1235315132' from your second example as there is a space after 12345'

    If you want to explicitly pull 5 digits then you can replace the + with {5}

    REGEXP_REPLACE(`field`, '^.Purchase Order (\d{5}).$', '$1')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories