Regular Expression Functionality in SQL Server 2000

Document Filters on Image Columns

In image columns, SQL Server 2000 can store documents of various types. For example, a number of Microsoft Word documents can be stored in such a column. SQL Server has several built-in filters that allow documents contained in image columns to be processed so that their textual content can be indexed and searched.

Storing multiple documents in an image column can be useful to search multiple documents where fulltext search functionality is required.

Exercises

The following exercises test your understanding of some of the new material introduced in this chapter:

1.Using the pubs database, create Transact-SQL code that will match only the surnames Green and Greene. Hint: Use the pattern G% to find out which surnames beginning with G are in the pubs database.

2.Using the pubs database, create Transact-SQL code that will match book titles containing the character sequence data. Hint: Book titles are contained in the dbo.titles table in the pubs database.

391

17

Using Regular Expressions

with MySQL

MySQL is a relational database that aims to compete with longer-established commercial relational database management systems such as IBM’s DB2 and Microsoft’s SQL Server. While MySQL lacks some features present in the major players in the enterprise relational database management system market, it is a powerful and flexible database management system.

MySQL has extensive regular expression support, which allows powerful and flexible searching of textual data held in a MySQL database.

In this chapter, you will learn the following:

What metacharacters MySQL supports

How to use the SQL metacharacter _ and %

How to use the REGEXP functionality in MySQL

The functionality described here is present in MySQL version 4.0, the version recommended for production use at the time of this writing. However, a beta of MySQL 4.1 was in development, as well as an alpha of MySQL version 5.0. The regular expression support described in this chapter is anticipated to continue in versions 4.1 and 5.0 but is subject to the usual uncertainties of software in development.

Getting Star ted with MySQL

The MySQL database product can be downloaded from www.mysql.com. At the time of this writing, the MySQL download page is located at http://dev.mysql.com/downloads.

If you are installing it on Windows, select the desired version (production or an alpha or beta version, according to your interests) suitable for Windows. The examples were run and tested on MySQL 4.0.

Chapter 17

Unzip the downloaded file to a temporary directory. From the temporary directory, run the Setup.exe file. The examples in this chapter assume that you have installed MySQL to the c:\mysql directory. If you install it to some other location, you will need to adjust some of the step-by-step instructions accordingly.

On Windows XP, MySQL runs as a Windows service. Depending on whether you have had earlier versions of MySQL installed, you may find that you need to start the MySQL service manually. From the Start button, select Control Panel. Assuming that you are using the Classic configuration of Control Panel, select Administrative Tools, select Services, and then navigate to the MySql service. The status of the service will be displayed. If the status column for the MySql service is blank, with the MySql service highlighted, use the Start link towards the upper-left corner of the Services window to start the MySql service.

Open a command window. Assuming that you installed MySQL to c:\mysql, navigate to the c:\ mysql\bin directory. At the command line, type the following:

mysql

The mysql utility should start. In the examples that follow in this chapter, you will issue SQL commands from the mysql utility’s command line.

Assuming that MySQL is installed and the MySql service is running, you should see a screen that appears similar to that shown in Figure 17-1 when you type mysql.

Figure 17-1

The examples in this chapter will be run against a database called BRegExp. First, you need to create it.

The names of database objects in MySQL on the Windows platform are case sensitive, with the exception of the databases themselves. The reason for this is that MySQL databases are held as operating system files. The Windows operating system does not support case-sensitive filenames; therefore, MySQL, on Windows, behaves as though database names are case insensitive. On Unix and Linux, MySQL database names are case sensitive, so they behave like other MySQL database objects.

To create the BRegExp database, issue the following command at the mysql command-line prompt:

CREATE DATABASE BRegExp;

Be sure to include the semicolon at the end of the command, or the mysql utility will wait until you do. For more complex SQL commands, I find it convenient to spread the clauses across several lines, which aids readability.

Incremental versions of MySQL 4.0 have changed the default permissions on database objects from earlier versions. The intent is to improve security, but the concomitant effect is a loss of ease of use. You may find that you need to take time to study the permissions documentation of the version that you download. The instructions in this chapter assume that you have configured MySQL permissions informed by the documentation for the version you are using.

394

Using Regular Expressions with MySQL

If the BRegExp database has been created successfully, you should see a screen that appears similar to that shown in Figure 17-2.

Figure 17-2

At the mysql command line, issue the following command to switch to using the BRegExp database:

USE BRegExp;

You should see a message similar to the following:

Database Changed

You can now create tables in the BRegExp database against which to run SQL queries that contain regular expressions.

Exit the mysql utility by typing EXIT at the mysql command line.

The first table you will add to the BRegExp database will allow you to explore some simple SQL regular expression constructs.

The SQL script, People.sql, creates a table People in the BRegExp database and then adds some sample data to the table. Setting the supplied values for the ID column to NULL allows MySQL to provide an autoincremented value for the ID column. It is shown here:

USE BRegExp;

CREATE TABLE People

(ID INT PRIMARY KEY AUTO_INCREMENT,

LastName VARCHAR(20),

FirstName VARCHAR(20),

DateOfBirth DATE);

INSERT INTO People

(ID, LastName, FirstName, DateOfBirth)

VALUES

(NULL, ‘Smith’, ‘George’, ‘1959-11-11’),

(NULL, ‘Armada’, ‘Francis’, ‘1971-03-08’),

(NULL, ‘Schmidt’, ‘Georg’, ‘1981-10-09’), (NULL, ‘Clingon’, ‘David’, ‘1944-11-01’), (NULL, ‘Dalek’, ‘Eve’, ‘1953-04-04’), (NULL, ‘Bush’, ‘Harold’, ‘1939-11-08’), (NULL, ‘Burns’, ‘Geoffrey’, ‘1960-08-02’), (NULL, ‘Builth’, ‘Wellstone’, ‘1947-10-05’), (NULL, ‘Thomas’, ‘Dylan’, ‘1984-07-07’), (NULL, ‘LLareggub’, ‘Dai’, ‘1950-11-02’), (NULL, ‘Barns’, ‘Samuel’, ‘1944-06-01’),

(NULL, ‘Claverhouse’, ‘Henry’, ‘1931-08-12’), (NULL, ‘Litmus’, ‘Susie’, ‘1954-11-03’);

395

Chapter 17

The following command assumes that you have downloaded the file to a location in the c:\BRegExp\ Ch17 directory and that you have a command window open with the current directory being the bin directory for MySQL. Issue the following command at the operating system command line:

mysql <c:\BRegExp\Ch17\People.sql

The < character indicates the location of a SQL script that the mysql utility is to execute.

If the script has executed successfully, the command prompt is displayed with no error messages showing.

You can confirm that the table has been successfully created by running the mysql utility. After the mysql utility has started, issue the following commands at the command line:

USE BRegExp;

Then issue the following:

SELECT * FROM People;

If the script has run successfully, you should see a screen similar in appearance to that shown in Figure 17-3, with the content of the People table displayed.

Figure 17-3

The Metacharacters MySQL Suppor ts

MySQL supports a useful range of metacharacters, some derived from SQL syntax and some from regular expressions syntax.

The following tables summarize regular expression support in MySQL 4.0. The first table lists the SQL metacharacters that are used with the LIKE keyword. The second table lists the regular expression metacharacters that are used with the REGEXP keyword.

396