I have extolled the virtues of MySQL in Uplink because the program is insanely fast, rock-solid stable and free of cost. But over the past few months, I have discovered this open-source database server has a few more tricks up its sleeve. MySQL supports an extremely advanced set of regular expression functions that make searching databases easier and more precise. I recently investigated this feature when I was trying to find a way to efficiently join two tables of data by conditionally matching all or part of a company name.
I wanted a way to search for various combinations of 'Kellogg, Brown and Root' in a single query, and that's exactly what regular expressions allowed me to do. The REGEXP operator can be used in the place of a LIKE clause in SQL, including in joins. Regular expressions offer a great deal more precision over a wildcard search. GROUP_CONCAT is a new function for MySQL as of version 4.1, and I have found it particularly useful for aggregating non-numeric data.
Say you have a number of ZIP codes and a number of towns within each ZIP, and you want to know in one query all the towns contained in each ZIP code. MySQL also includes a useful function that allows users to get not only aggregates but also subtotals. This function is a modifier to the GROUP BY clause, called ROLLUP. The records with the NULL value are subtotals, and the final record is a grand total.
MySQL includes 95 percent of the features CAR folks use 95 percent of the time, as well as a few features the other ones lack. With version 5.0, MySQL will add stored procedures. Even if it does not become your platform of choice for CAR work, it is worth investigating as an alternative to the high-priced database servers.