regex extract big query with numeric data

Question

how would I be able to grab the number 2627995 from this string

"hellotest/2627995?hl=en"

I want to grab the number 2627995, here is my current regex but it does not work when I use regex extract from big query

(\/)\d{7,7}

SELECT
  REGEXP_EXTRACT(DESC, r"(\/)\d{7,7}")
  AS number
FROM
  `string table`

here is the output

Thank you!!

The slash is not a special character in regex: You neither need to use brackets (no need to capture it) nor escape it. ie \/ is identical to just / — Bohemian
– Bohemian ♦, Commented Jun 24, 2021 at 5:16

Tim Biegeleisen · Accepted Answer · 2021-06-24 04:06:00Z

1

I think you just want to match all digits coming after the last path separator, before either the start of the query parameter, or the end of the URL.

SELECT REGEXP_EXTRACT(DESC, r"/(\d+)(?:\?|$)") AS number
FROM `string table`

Demo

answered Jun 24, 2021 at 4:06

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sergey Geron · Accepted Answer · 2021-06-24 04:08:40Z

0

Try this one: r"\/(\d+)"

answered Jun 24, 2021 at 4:08

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

Comments

Wiktor Stribiżew · Accepted Answer · 2021-06-24 08:09:46Z

Your code returns the slash because you captured it (see the parentheses in (\/)\d{7,7}). REGEXP_EXTRACT only returns the captured substring.

Thus, you could just wrap the other part of your regex with the parentheses:

SELECT
  REGEXP_EXTRACT(DESC, r"/(\d{7})")
  AS number
FROM
  `string table`

NOTE:

In BigQuery, regex is specified with string literals, not regex literals (that are usually delimited with forward slashes), that is why you do not need to escape the / char (it is not a special regex metacharacter)
{7,7} is equal to {7} limiting quantifier, meaning seven occurrences.

Also, if you are sure the number is at the end of string or is followed with a query string, you can enhance it as

REGEXP_EXTRACT(DESC, r"/(\d+)(?:[?#]|$)")

where the regex means

/ - a / char
(\d+) - Group 1 (the actual output): one or more digits
(?:[?#]|$) - either ? or # char, or end of string.

Collectives™ on Stack Overflow

regex extract big query with numeric data

3 Answers 3

Demo

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related