Wednesday, June 25, 2008

No Alphabetic Characters Wanted

Here is a question that was posed to me recently:


Q: We have a CHAR(10) column that cannot contain alphabetic characters. How can we make sure that the letters A thru Z are not allowed.


A: Well, think about the characteristics of alphabetic characters versus the other "things" that can be stored in a CHAR column. One thing that separates an alphabetic letter from numbers, punctuation, etc. is that there are upper and lower case versions (e.g. A, a). So, you could use the following predicate to preclude alphabetic characters from being accepted:

LOWER(:string) = UPPER(:string)

Of course, you will not be able to put this into a CHECK constraint because of restrictions on their content (for example, you cannot use function in a CHECK constraint). But you could use this in SQL statements and as a check in your programs before allowing data to be inserted or modified in your CHAR(10) column.

Anyone else have any other ideas?

2 comments:

Anonymous said...

Hi Craig,

what about a trigger like

CREATE TRIGGER ... NO CASCADE
BEFORE INSERT ON ...
REFERENCING NEW AS N
FOR EACH ROW MODE DB2 SQL
WHEN (UPPER(N.string <>
LOWER(N.string))
SIGNAL SQLSTATE
'75001' ('invalid string')

Craig S. Mullins said...

A trigger like that should work nicely.