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.)

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 below.
|> In the New Constraint dialog box, click Add.
|> Click Close.
|> Click Save on the Standard menu.

A simple expression rejects any alpha character

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\')

The image below 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

The CHECK constraint accepts five number characters

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 below.

The CHECK constraint rejects any entry that contains an alpha character

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.

The image below shows what happens when you try to enter a value that's fewer than nine characters or that contains an alpha character -- the table accepts only one value, 406045555.

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 defence. 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