SUBSTRING_INDEX with multiple delimiters

[ncurran217]

I am trying to remove some information off urls that are placed into my database. I have this query that I am using:

 

Select substring_index(refurl,'?gclid',1) as refurl, Count(*) from leads group by substring_index(refurl,'?gclid',1)

 

But for the delimiter I want it to clear off ?gclid or &gclid, is this possible by doing an OR statement within the substring or is it something completely different to get this done?

[Barand]

You could do something like this

mysql> SELECT * FROM test1;
+----+--------------------+------+
| id | http_referrer      | hit  |
+----+--------------------+------+
|  1 | www.google.com?abc |    3 |
|  2 | www.google.com&def |    5 |
|  3 | www.yahoo.com?xyz  |   10 |
|  4 | www.yahoo.com&tuv  |    4 |
+----+--------------------+------+

SELECT
    CASE
        WHEN LOCATE('?',http_referrer )>0 THEN SUBSTRING_INDEX(http_referrer, '?', 1)
        WHEN LOCATE('&', http_referrer)>0 THEN SUBSTRING_INDEX(http_referrer, '&', 1)
        ELSE http_referrer
    END as referrer
, SUM(hit) as hits
FROM test1
GROUP BY referrer

+----------------+------+
| referrer       | hits |
+----------------+------+
| www.google.com |    8 |
| www.yahoo.com  |   14 |
+----------------+------+