Use Multiple Columns in Regex in BigQuery

Question

I want to use regex_replace to remove phrases that exist in one (or both) of two columns. So from the product name I want to remove the colour and brand that exist in the colour or brand columns. I have regex like this, but I wonder if you can do it without nesting?

    regexp_replace(regexp_replace(lower(product_name), 
    lower(Manufacturer_Brand_Name), ''),lower(Colour), '')

so instead of the above have something like

regexp_replace(lower(product_name), 
    (lower(Manufacturer_Brand_Name)|lower(Colour)), '')

But obviously you have to put it in quotation marks to read as regex, so then I'm not actually inputting the column names

regexp_replace(lower(product_name), 
    '(lower(Manufacturer_Brand_Name)|lower(Colour))', '')

The expected result is Dell Laptop Blue to just Laptop

Mikhail Berlyant · Accepted Answer · 2019-07-18 13:19:51Z

0

You should use

REGEXP_REPLACE(product_name, CONCAT(r'(?i)', Manufacturer_Brand_Name, '|', Colour), '')

For example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Dell Laptop Blue' product_name, 'Dell' Manufacturer_Brand_Name, 'blue' Colour
)
SELECT 
  REGEXP_REPLACE(product_name, CONCAT(r'(?i)', Manufacturer_Brand_Name, '|', Colour), '')
FROM `project.dataset.table`

with result

Row f0_  
1   Laptop

answered Jul 18, 2019 at 13:19

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

Collectives™ on Stack Overflow

Use Multiple Columns in Regex in BigQuery

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related