Baseball Hacks

Use Regular Expressions to Identify Events

Use the regular expression tools in MySQL to find events that match a pattern.

Sometimes you want to parse every event and calculate every statistic; other times, it’s only important to find the events that match a pattern. This hack shows you how to use regular expressions for pattern matching. You can use this technique to create smaller files that you can open in a spreadsheet.

If you’ve imported play-by-play data into a MySQL database like the one we built in “Make a Historical Play-by-Play Database” [Hack #22] , you can select lines using regular expressions.

Let’s start with an easy example. Suppose you want to find all plays where the batter struck out. We know the play code for a strikeout always starts with K (see “Keep Score, Project Scoresheet–Style” [Hack #3] for information on play codes). To do this, we use the REGEXP operator in MySQL. This operator returns true if the field matches the regular expression, and false otherwise.

First, let’s select the right database in MySQL:

	~/Desktop % mysql pbp
	Reading table information for completion of table and column names
	You can turn off this feature to get a quicker startup with -A

	Welcome to the MySQL monitor.  Commands end with ; or \g.
	Your MySQL connection id is 3 to server version: 5.0.11-beta-max

	Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Now, let’s select a few items:

 mysql> select game_id, visiting_team as vis, -> concat(case when batting_team=1 THEN "B" ...