Extract multiple sets of numbers from a single string

Gojo
Gojo Member
in Magic ETL

I am trying to extract two different sets of numbers from a string. I have tried substring and Regexp, but to no avail.

Examples of the stringss

-

Possible string format #1

Company Name - 2024-07-01-2024-07-31 - GPPC - YTUBE - VYT - 63784 - 2782837 - 458573682 - [745-000121-003]

Possible string format #2

Company Name - 2024-07-01-2024-07-31 - GPPC - YTUBE - VYT - 63784 - 2782885 - 458573695

-

The only difference between the two possible formats is that the number on the end surrounded in brackets may not appear on some strings. In the example above, i would like to pull "2782837" or "2782885" and "458573682" or '458573695' respectively. What would be the best way to accomplish this in the ETL, using sql or the tiles or both?

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    If they're always in the same position with the same ' - ' separator you can use SPLIT_PART

    SPLIT_PART(`string`, ' - ', 7)
SPLIT_PART(`string`, ' - ', 8)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Gojo
    Gojo Member

    Hi Grant, awesome, thank you it did work for any of the strings ending in " [745-000121-003]" but it did not seem to work, if the string ended without the bracket [] e.g. 2782885 - 458573695. Do you know why that is and how I can make sure it works for both?

  • GrantSmith
    GrantSmith Coach

    How did it not work? Did it return the wrong numbers or not return anything?

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

    It returned a blank field. I was trying to play around with it, but no luck. I am not sure why it only works for the strings that included the number inside the brackets.

  • rco
    rco Domo Employee

    I tried both expressions with both the strings in your example and it seemed to work in all cases. Are you certain that the exact string on which it isn't working is this one?

    Company Name - 2024-07-01-2024-07-31 - GPPC - YTUBE - VYT - 63784 - 2782885 - 458573695

    Randall Oveson <randall.oveson@domo.com>

  • Gojo
    Gojo Member

    Hi rco, perhaps the issue, is that the strings without the brackets are a bit shorter. Please see the examples below, would that effect it?

    Community - 2.13 - 2.28 - 2962953 - 502020281

    Company - 2.24 – 3. 30 – 2936712 - 503631860

    Thank you for you help.

  • Gojo
    Gojo Member

    I see what I did wrong. thank you guys for all your helP!

Categories