MySQL Tricks: Splitting Strings

 in MySQL  tagged WordPress by

I needed to run a query to find out how many people in our user table had an email address on a domain within a set list from another table.  Normally, I might have written a PHP script for this, pulling users that matched a loose LIKE statement of email_address LIKE ‘%tamu.edu’ or the like, then looped those, did a split, and checked it against the domain table.

However, I decided to see if there was a way I could do it in a single MySQL query and, much to my happiness, there was thanks to MySQL’s substring_index function!  This function allows you specific a string (or column), a delimiter, and then a “count”.  With the count, you specify which how many instances of the delimiter to go to, then you either use a positive or negative number to indicate you want the rest of the string to the left or right (respectively) of that delimiter.

So, for example:

	SELECT substring_index('sswilson@someplace.com', '@', 1)
	 -> sswilson

	SELECT substring_index('sswilson@someplace.com', '@', -1)
	 -> someplace.com

With that knowledge in hand, I could then write a query to get what I needed:

	SELECT id, name, email, substring_index(email, '@', -1) as domain
	FROM people
	WHERE substring_index(email, '@', -1) IN (SELECT domain FROM domains)
	ORDER BY domain, email
	;

You could also use having, if you prefer

	SELECT id, name, email, substring_index(email, '@', -1) as domain
	FROM people
	HAVING domain IN (SELECT domain FROM domains)
	ORDER BY domain, email
	;

One quick cautionary note – it does do a case sensitive match!  So, if you need to make sure you pick up any case variant, flip the case:

	SELECT id, name, email, substring_index(lcase(email), '@', -1) as domain
	FROM people
	HAVING domain IN (SELECT domain FROM domains)
	ORDER BY domain, email
	;

I also learned about another MySQL function recently, but now I’m darned if I can remember it (what happens when I don’t make notes to blog about something when I run into it).  When I remember, though, I’ll post about it 🙂

Meanwhile, I am still working on the conversion to WordPress – done all the overtime I’d be working since March so trying to get back on track with a bunch of stuff.  I have made a few cursory attempts at writing import scripts for the Mango to WordPress conversion but it’s still a work in progress.  I’m also still working on deciding what theme I want to use.  I did at least find and confirm that Syntax Highlighter available for WordPress too, so I don’t have to fudge around with converting my old code!