How do I… Reject alpha characters in a SQL Server character column?
Enforcing your data’s integrity is probably the single most important issue you face when designing a database. Validating user input is one way of keeping bad data from making its way into your analyses and reports. It takes only one piece of bad data to throw everything off. It’s serious business and there are no shortcuts — mistakes, even innocent ones, are easy to make. It’s just too easy to enter one too many zeros or enter “6heodore” instead of “Theodore.” Granted, you can’t stop every single mistake at the input stage, but it’s the best place to start. (We are working with SQL Server Express because it’s free and easy to use, but the concept and examples are valid in SQL Server.)
This blog post is also available in PDF form as a TechRepublic download.
About CHECK
When you restrict the values a column can store, you’re enforcing domain integrity. In other words, you’re making sure that all of the data for a single column (or a table) meets specific conditions. SQL Server offers two built-in constraints for enforcing domain integrity:
- CHECK is a rule that applies to data. You might use a CHECK constraint to require a value for a particular field to be greater than 1 or to contain a specific number of characters.
- DEFAULT fills in a value when the user fails to supply one. (We are not discussing DEFAULT here.)
Using CHECK to reject alpha characters
The easiest way to reject alpha characters is to use a numeric data type, but that’s not always practical. Sometimes, you want a character column to contain only numeric values. This happens when valid data consists of numeric characters, but you don’t use them mathematically. For instance, phone numbers and ZIP codes comprise numeric characters, but you store them as text because you won’t evaluate them in mathematical equations.
A character column accepts both numbers and letters. If domain integrity requires that such a column accept only numbers, use CHECK to make that happen. It’s easier than you might think. Simply specify a numeric character for each required position. A character column that stores ZIP code values is a good example.
Using Management Studio, expand a database and add a new single-column table. Name the column PostalCode and set its data type as char(5). Name the table anything you like and expand the new table’s node. To add a constraint that rejects any entry that contains an alpha character, do the following:
- Right-click the table’s Constraints node and choose New Constraint.
- Enter (PostalCode LIKE ‘[0-9][0-9][0-9][0-9][0-9]’) in the Expression text box to limit the first five characters to numbers. Be careful not to enter space characters between the digit component [0-9].)
- Modify the constraint’s default name to CK_PostalCodes_PostalCode.
- Enter a description, such as “Limit ZIP code values to numbers,” as shown in Figure A.
- In the New Constraint dialog box, click Add.
- Click Close.
- Click Save on the Standard menu.
Figure A
A simple expression rejects any alpha character
Now, check the constraint by trying to adding a couple of records. First, run the simple query:
USE AdventureWorks
INSERT PostalCodes
VALUES ('40604')
Figure B shows what happens when the ZIP code value satisfies the CHECK constraint — SQL Server adds a new record for the ZIP code value. Now, run this second query
USE AdventureWorks
INSERT PostalCodes
VALUES ('r0604')
The value has an alpha character — r instead of 4 — but the CHECK constraint catches your typo, as you can see in Figure C.
Figure B
The CHECK constraint accepts five number characters
Figure C
The CHECK constraint rejects any entry that contains an alpha character
Add CHECK programmatically
You can add a CHECK constraint programmatically when you create the table or after by executing a Transact-SQL (T-SQL) ALTER TABLE statement. The following T-SQL adds a new column to the example table:
USE AdventureWorks ALTER TABLE dbo.PostalCodes ADD PostalCodeExtended char(9) NULL GO ALTER TABLE dbo.PostalCodes ADD CONSTRAINT CK_PostalCodes_PostalCodeExtended CHECK (PostalCodeExtended LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') GO
The first statement adds a character column named PostalCodeExtended. The next statement uses the ADD CONSTRAINT clause to add a CHECK constraint that forces number characters. The only difference is that this column expects a nine-character ZIP code value. Similarly to the manual example, the constraint will reject any value that contains an alpha character.
Figure D shows what happens when you try to enter a value that’s fewer than nine characters or that contains an alpha character. In this case, the table accepts only one value, 406045555.
Figure D
Both the data type and the constraint reject invalid values
You’re out!
Laying siege to bad data from the get-go will reduce mistakes and the headaches later on. Defining the appropriate data type is your first line of defense. When data types aren’t enough, SQL Server’s CHECK constraint is a powerful ally.
If you’ve upgraded a legacy database that’s still using rules (the predecessor to constraints), convert them to constraints. There’s no guarantee that subsequent versions of SQL Server will continue to support rules. Converting rules before they’re extinct will save you a lot of time and trouble later on.
To learn more about constraints visit:
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is “Mastering Microsoft SQL Server 2005 Express,” with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are “Automating Microsoft Access 2003 with VBA,” “Upgrader’s Guide to Microsoft Office System 2003,” “ICDL Exam Cram 2,” and “Absolute Beginner’s Guide to Microsoft Access 2003″ all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at ssharkins@gmail.com.
