1.4K members online now
1.4K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

REGEXP_EXTRACT only returns null

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi,

 

I wonder if anyone has had any success with REGEXP_EXTRACT; For me, it seems to only ever return "null". Maybe Iam expecting it to do something that it was not intended for.

 

Column name (dimension): os

example value: Android 6.0.1

 

new calculated dimension: osVer

Formula: REGEXP_EXTRACT(os, '\\d')

result: osVer contains only "null"

 

I have tried with many expressions, including '[0-9]' and even  '.'  The result is always the same. 

Am I missing something?

REGEXP_EXTRACT only returns null

Explorer ✭ ✭ ☆
# 2
Explorer ✭ ✭ ☆

What are you trying to extract out of the original dimension os?

REGEXP_EXTRACT only returns null

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Sian.

 

a typical value in os would be "iOS 11.1.1" and I'm looking to create a new dimension that would contain "1111" which I would then cast to a number type.

 

Marked as Best Answer.
Solution
Accepted by topic author Matthew M
1 month ago

REGEXP_EXTRACT only returns null

Explorer ✭ ✭ ☆
# 4
Explorer ✭ ✭ ☆

It's a tricky RegEx because of the dots between the numbers and the fact that an iOS version might not always have the three parts.

Instead you could use the REPLACE function twice to remove the leading text and then the dots.

 

REPLACE(REPLACE(os, "iOS ", ""), ".", "")

 

You can then include the CAST function in the same calculated field to ensure it's a number.

REGEXP_EXTRACT only returns null

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Thanks Sian, trying now...

REGEXP_EXTRACT only returns null

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

Great thanks! 

 

this is what finally worked:

 

CAST(REPLACE(REPLACE(REPLACE(os, 'iOS ', ''), '.', ""), 'Android ', '') AS Number )

 

I very much appreciate your help, hope you have a great weekend Smiley Happy

REGEXP_EXTRACT only returns null

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Sian M's solution works in this particular case, but I am still having the same trouble as the OP.  I have a String field (Email) that contains a bunch of text, followed by a dot and four numerals.  I've tried several different methods of extracting these four digits

 

For example, I'd like to take the String "Mike.R.2006@domain.org" and extract the digits "2006"  

Bearing in mind that these strings are all a different length due to differing first names, it seems that the most efficient RegEx would be:

REGEXP_EXTRACT(Email, '(:digitSmiley Happy{4}')

 

However, even just a plain old 

REGEXP_EXTRACT(Email, '(:digitSmiley Happy')

 

Returns "null".  Does anyone have a functioning example of REGEXP_EXTRACT at this time?

REGEXP_EXTRACT only returns null

Explorer ✭ ✭ ☆
# 8
Explorer ✭ ✭ ☆

Hi Mike,

 

Try REGEXEXTRACT(Email,"[\d]{4}")

 

 

Re: REGEXP_EXTRACT only returns null

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

Hi, and thank you for the suggestion!  Unfortunately, it did not work (not able to parse, see screenshot below).Data Studio - Could not parse.png

 

 

Here is the way the data looks (domain removed for privacy):Want to extract the four digits that precede the @.Want to extract the four digits that precede the @.

 

 

 

REGEXP_EXTRACT only returns null

Explorer ✭ ✭ ☆
# 10
Explorer ✭ ✭ ☆

Apologies, there was a slight misspelling in my original formula.

 

It should be REGEXP_EXTRACT rather than REGEXEXTRACT. I'm not sure why, but Data Studio uses a different function name to Google Sheets.

 

Whilst the formula I suggested works in Sheets, I also can't get it to work in DS!

 

Instead of looking for a set of four digits, you could look for the string between the second . and the @.

REGEXP_EXTRACT(Email, '^(?:[^.]*.){2}([^@]*).*')

 

This obviously assumes that all emails follow the syntax of the three you have shown in the image.