Chapter 17

Social Security Number Example

U.S. Social Security numbers (SSNs) are a classic example of the use of regular expressions for pattern matching. The history of U.S. SSNs is complex. Living people may have had their SSN allocated many years before the current system was implemented.

One relatively simple pattern that you can use to match an SSN is as follows:

[0-9]{3}-[0-9]{2}-[0-9]{4}

MySQL does not support the \d metacharacter to match numeric digits, so you need to use the character class [0-9] instead.

You can use that pattern to see if all employee records contain a valid Social Security number.

Try It Out

Matching Social Security Numbers

These instructions assume that the mysql utility is running and that BRegExp is the current database.

1.At the mysql command prompt, type the following command:

SELECT ID, LastName, FirstName, SSN FROM Employees

WHERE SSN REGEXP ‘^[0-9]{3}-[0-9]{2}-[0-9]{4}$’

;

Because you want to match only if the whole field is a valid SSN, you wrap the pattern shown earlier inside the ^ and $ metacharacters.

2.Inspect the results to identify ID numbers not displayed. Figure 17-18 shows that each row’s displayed value in the SSN column conforms to the pattern three digits, a hyphen, two digits, a hyphen, and four digits. So you can confirm that the pattern will match valid SSNs.

Figure 17-18

The NOT keyword is not supported in association with the REGEXP keyword; therefore, you can’t directly identify rows with invalid values in the SSN column.

410

Using Regular Expressions with MySQL

The lack of support for lookahead and lookbehind limits further refinement of the SSN pattern in MySQL. If lookahead were supported, you could specify, by syntax such as ^(?!000), which is not supported in MySQL, that the first three numeric digits of the SSN couldn’t be 000. The official description of the SSN located at www.ssa.gov/foia/stateweb.html indicates that the first three digits of a valid SSN will never be 000. As indicated at the same URL, more area numbers (the first three digits) are likely to be put into use in the near future. If you wish to create a highly specific pattern to match SSNs, you need to monitor that URL to see what the current position is.

Exercises

1.Write a line of SQL code to determine whether the pattern 195% matches the date 1950-01-01.

2.Write some SQL code to display employees who have .NET skills. Display the ID, last name, and first name, together with the description of the employee’s skills.

411