MySQL Regex or Split

Eric Jensen eric at emstraffic.com
Wed Apr 27 12:37:44 MDT 2005


Steve Jibson wrote:

> You can do regular expression matching in a SELECT with MySQL.  (see
> http://dev.mysql.com/doc/mysql/en/regexp.html).  I think that should
> give you the ability to do what you want to do.
>
> As far as the exact expression you want... well, rather than show off
> my ignorance, I'll just sit here silently and let you think that I'm
> smarter than I really am.
>
REGEXP will return yay or nay, which wont work for what I want.  I was
needing to basically group by a partial string.  I don't know what all
the domains I need to group by are ahead of time so I need to extract
that from the URLs already in the database.  I ended up using
`substring_index(substring_index(referer,'/',3),'/',-1) AS referer`,
which finds the 3rd occurance of '/' and returns everything before that
and then find the -1 occurance of / and returns what is before that.  So
it gives me "www.google.com" out of "http://www.google.com/".  And that
is now a format I can properly group by.  I have 2 million entries in
the database and it grouped them all incredibly fast.

Eric Jensen



More information about the PLUG mailing list