Jump to content


Photo

SUBSTRING_INDEX with multiple delimiters

mysql

  • Please log in to reply
1 reply to this topic

#1 ncurran217

ncurran217

    Advanced Member

  • Members
  • PipPipPip
  • 81 posts

Posted 19 March 2014 - 07:46 PM

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?

#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,174 posts
  • LocationCheshire, UK

Posted 20 March 2014 - 04:31 AM

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 |
+----------------+------+

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com