SUBSTRING_INDEX with multiple delimiters

Question

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 really need to take off both of these:

?gclid or &gclid

Is this possible by doing an OR statement within the substring or is it something completely different to get this done?

Thanks @Gordon for cleaning it up. I will make sure I do that before I submit the question. — NickC217, Mar 20 '14 at 01:35

Barmar · Accepted Answer · 2014-03-20T01:48:21.090

7

Use IF

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

edited Mar 20 '14 at 01:48

answered Mar 20 '14 at 01:21

Barmar

741,623
53
500
612

That does not work. It says unknown column 'a' in field list. If I change the a to a 1, it only takes away &gclid and not the urls with ?gclid – NickC217 Mar 20 '14 at 01:30
Sorry, that was a typo, it should be `1`. – Barmar Mar 20 '14 at 01:35
But when I changed it to one it didnt take off the urls with ?gclid though only &gclid worked. – NickC217 Mar 20 '14 at 01:36
Oops, didn't you notice that I got the arguments to `LOCATE` backwards? Fixed it. – Barmar Mar 20 '14 at 01:48
haha I just figured that out as well. Thanks again works great now I have them in the right places. – NickC217 Mar 20 '14 at 01:54

score 2 · Answer 2 · answered Mar 27 '18 at 07:32

Just in case, I just needed to group strings before a certain number, ex:

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 obtain only this:

AQP Casma
Cusco

I couldn't use IF because the multiple delimiters, so the query that I've used is:

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

And the result was the expected... I don't know why is grouping correctly all the Cusco registers, but It's working as I needed.

Hope it could help.

Bye.

SUBSTRING_INDEX with multiple delimiters

2 Answers2

Linked