Extract strings before a regex in BigQuery

Question

I am new to regex. I am trying to extract the website name from the long URL like below.

http://connect.themediatrust.com/d/?a=19481782541

I am trying to get everything before ".com" part.

SELECT
  customer_id,
  COALESCE(REGEXP_EXTRACT(referrer_uri, r'([^(.+?)".com"])'),referrer_uri) AS referrer_uri
FROM
  [zz_marketing_analytics.visit_from_zumobi]
WHERE
  referrer_uri="http://connect.themediatrust.com/d/?a=19481782541";

I am getting only "h" as refer URI. I'm not sure what's wrong.

See SO search results for REGEXP_EXTRACT used with Big Query. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Nov 28, 2016 at 20:17

Mikhail Berlyant · Accepted Answer · 2016-11-28 21:20:28Z

3

I am trying to get everything before ".com" part.

Try below to start with

SELECT 
  REGEXP_EXTRACT(referrer_uri, r'(.*).com') AS referrer_uri,
  DOMAIN(referrer_uri), 
  HOST(referrer_uri)
FROM (SELECT "http://connect.themediatrust.com/d/?a=19481782541" AS referrer_uri)

answered Nov 28, 2016 at 21:20

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

SAM244776 Over a year ago

Did not realize there is a host function; very helpful :)

Collectives™ on Stack Overflow

Extract strings before a regex in BigQuery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related