CODEWITHSUNDEEP
javascriptgoogle-analyticsgoogle-bigquery
Nathaniel Payne
Nathaniel Payne

Reputation: 2827

Error with a BigQuery regular expression

This question builds off a previous question that I asked:

How do I remove the first character of a string and treat the remaining values as an integer in BigQuery

I am having trouble getting a regular expression that I need for some client work to function. Basically, I want to look through all the cells in a column which has the following types of entries:

customer-o400744190

o400748216

o455239157-new-customer

other similar types with o4552334214 somewhere in the cell

and use something like REGEX_EXTRACT() to parse out or extract "oXXXXXXXXX" from every cell & dump those values into a new column. The data in the column I am pulling from is stored in a string, and can stay that way. Does anyone have any suggestions?

I worked around the problem by just using:

RIGHT(hits_transaction_transactionId, 10)

but know that I am only getting some of the cases that apply. Thus, this is not an acceptable long term solution. Any ideas are greatly appreciated.

Upvotes: 0

Views: 388

Answers (1)

Javier Ramirez
Javier Ramirez

Reputation: 4032

Depending on how your data universe is, you could go with /o[0-9]+/

so you would be extracting any ocurrences of o and then at least one number

Upvotes: 1

Related Questions