CODEWITHSUNDEEP
sqlregexgoogle-bigquery
Jaskeil
Jaskeil

Reputation: 1232

Regex: Extract everything after forward slash in Google Bigquery?

I have two scenarios below. I would like to extract everything after search/ or between search/ and category/

Scenario 1, where I want everything after search/:

/listBuilder/20568284/search/1"X4"-8 t%26g

Scenario 2, where I want everything between search/ and category/:

listBuilder/20464071/search/decking screws/category/6765

Edit & Further Clarity

Say I have a column with the following information

Column1
/listBuilder/20568284/search/1"X4"-8 t%26g
listBuilder/20464071/search/decking screws/category/6765

From column1 how can I get a new column, using regex, with the following ideal information

Column2 
1"X4"-8 t%26g
decking screws

Upvotes: 0

Views: 2321

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below

select text, 
  regexp_extract(text, r'search/(.+)') as after_search,
  regexp_extract(text, r'search/(.+)category/') as between_search_and_category,
from `project.dataset.table`

if you need to handle/extract into one column - consider below

select regexp_extract(text, r'search/(.+?)(?:/category|$)')  
from `project.dataset.table`

If applied to sample data in your question - output is

enter image description here

Upvotes: 2

Related Questions