Matched REGEXP pattern in SELECT statement

Question

Imagine that there is clicks table which contains users clicks. Now I want to select the total clicks per social network:

UPD: refererHost column contains any referer host, not only social network host.

    SELECT
        c.refererHost as referer,
        COUNT(c.id) as clicks
    FROM clicks c
    WHERE c.referrerHost REGEXP 'facebook|google|linkedin'
    GROUP BY referer
    ORDER BY clicks desc

But the problem is that referer field will contain domain name like www.facebook.com, and I need to select matched regexp value (e.g. 'facebook'). Is it possible to do it with MySQL?

I don't think this is possible. As per documentation "Returns 1 if expr matches pat; otherwise it returns 0." dev.mysql.com/doc/refman/5.5/en/regexp.html — Florian Grell
– Florian Grell, Commented Sep 7, 2015 at 13:54
you can use REGEX_SUBSTR. SELECT REGEXP_SUBSTR("theDomain/…); It return only the names. — Bernd Buffen
– Bernd Buffen, Commented Sep 7, 2015 at 14:06
@BerndBuffen That's an Oracle SQL function.. I don't think there is an equivalent for MySQL I'm afraid — Arth
– Arth, Commented Sep 7, 2015 at 14:16
its also in MariaDB 10.0.11. mariadb.com/kb/en/mariadb/regexp_substr — Bernd Buffen
– Bernd Buffen, Commented Sep 7, 2015 at 14:18

Arth · Accepted Answer · 2015-09-07 14:17:40Z

1

Initially, I'd get rid of REGEXP entirely and go with:

  SELECT CASE 
           WHEN c.referrerHost LIKE '%facebook%' THEN 'facebook' 
           WHEN c.referrerHost LIKE '%google%'   THEN 'google' 
           WHEN c.referrerHost LIKE '%linkedin%' THEN 'linkedin'
         END referrer,
         COUNT(c.id) as clicks
    FROM clicks c
   WHERE c.referrerHost LIKE '%facebook%'
      OR c.referrerHost LIKE '%google%'  
      OR c.referrerHost LIKE '%linkedin%' 
GROUP BY referer
ORDER BY clicks desc

I'm fairly certain you'll get better performance, but you can benchmark if need be.

Ideally, however, I'd be tempted to build a proper referral system with a table:

referer - id, host, title
click - id, referrer_id

You can work out the referral on entry to your site, easily configured with a referrer_id if you control the urls.

Then your SQL becomes:

  SELECT r.title as referer,
         COUNT(c.id) as clicks
    FROM clicks c
    JOIN referrer r
      ON r.id = c.referrer_id
GROUP BY referer
ORDER BY clicks desc

edited Sep 7, 2015 at 14:17

answered Sep 7, 2015 at 14:10

Arth

13.1k5 gold badges43 silver badges72 bronze badges

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

1 Comment

Serhii Smirnov Over a year ago

One problem I faced with: I have "referer" field in the database, so group by grouped by original field, not by resulted referer field by CASE...END statement. Renaming field to "ref" helped me.

Collectives™ on Stack Overflow

Matched REGEXP pattern in SELECT statement

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