SUBSTRING_INDEX with multiple delimiters

Question

I am trying to remove some information from the URLs that fit into my database. I have this query that I use:

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

But for the separator, I really need to remove both of them:

?gclid or &gclid 

Is this possible by executing an OR statement inside a substring, or is it something completely different to do this?

Answer 1

Use IF

SELECT SUBSTRING_INDEX(refurl, IF(LOCATE('?gclid', refurl), '?gclid', '&gclid'), 1) AS refurl, ...

Answer 2

Just in case, I just needed to group the lines to a certain number, for example:

hcu
----------------------
AQP Casma 01 HCU 200
AQP Casma 10 HCU 1500
AQP Casma 11 HCU 1500
Cusco 1 HCU 1500
Cusco 2 HCU 200-3
Cusco 3 HCU 200-1
Cusco 12 HCU 200

to get just this:

AQP Casma
Cusco

I could not use IF because there are multiple delimiters, so I used the following query:

SELECT 
 CASE 
    WHEN LOCATE(' 0',hcu )>0 THEN SUBSTRING_INDEX(hcu, '0', 1)
    WHEN LOCATE(' 1',hcu )>0 THEN SUBSTRING_INDEX(hcu, '1', 1)  
END hub  
FROM npi_hist 
GROUP BY hub 

... , Cusco, , .

, .

Bye.