Extract multiple sets of numbers from a single string

Gojo · 2025-02-26T14:07:20+00:00

There was an error rendering this rich post.

Gojo

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?

Find more posts tagged with

SQL

REGEX

Magic ETL

Accepted answers

All comments

GrantSmith

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)

Gojo

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

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

Gojo

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

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

Gojo

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

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