It's been a while, so let's open up the old
mailbag!
Jeremy writes:
Can you spare a few minutes to show me a possible way to restrict a column in a table using a check constraint to only allow characters in a given range of the ASCII table?
I'm interested in filtering ASCII (numbers 0–31 decimal). Thanks!
Well, I don't completely understand what you mean by that second part (feel free to clarify in the comments), but perhaps I can help you out anyway. When writing constraints on a column, we often need to enforce things like "only allow letters a-z" and/or "only allow digits 0-9". It is usually easier, shorter and more correct to specify what we
will allow, rather than what we will
not allow, since it can be hard to anticipate (and type!) all of the characters that we wish to filter out.
Writing a CHECK constraint for that can be a little tricky, however. If we want to allow
only letters A-Z, then this:
CHECK Column LIKE '%[A-Z]%'
doesn't quite work, because that check will succeed if the column contains
at least one valid character, not
only valid characters. However, let's try to approach this from a different angle. The
opposite of the constraint that we want to ultimately have would be one that only allows strings with at least one
invalid character, right? Well, we can write that fairly easily by altering our like pattern like this:
CHECK Column LIKE '%[^A-Z]%'
The ^ symbol before A-Z means "not within the range of A-Z", so the condition above is only true if there are any characters outside of the range A-Z in the string. Since it is now the opposite of what we want, we can simply switch negate our expression by changing the LIKE to NOT LIKE and we have the constraint we need:
CHECK Column NOT LIKE '%[^A-Z]%'
The double-negative there can be confusing, but what we are saying is "allow data if there are NOT any characters that are NOT in the range of A-Z in the column." Which, is exactly what we want when we only want to allow the letters A-Z, just expressed rather backwards.
So, when you need to write a constraint similar to this, sometimes you need to be like
George Costanza and do the opposite of what you were thinking. It just might be exactly what you need.
Update (This is in the comments, but it may be useful to others) :
We know that LIKE '[A-Z]' allows all letters, and LIKE '[0-9]' allows numbers. It also turns out that
LIKE '[ -~]' (note the space before the - symbol)
allows all visible symbols in the ASCII character set between the space and the tilde, excluding all alphanumerics. So, to write a CHECK constraint that allows only the printable characters of the ASCII set, we can write:
CHECK column NOT LIKE '%[^ -~A-Z0-9]%'
Using the "opposite" logic explained above.