Solution to CHECK Constraint Puzzle

The problem I posed in the main article was to write a CHECK constraint that allows only digits in the column sn. Latin characters a thorough z and special characters aren't allowed. You need to apply the single-expression approach in your solution. When I give this puzzle to my students, most of them usually come up with the following expression:

sn NOT LIKE '%\[a-z\]%'

However, this solution doesn't take special characters into consideration. One possible solution is to replicate the string '\[0-9\]' as many times as there are characters in the sn column by using the REPLICATE() and LEN() functions:

sn LIKE REPLICATE('\[0-9\]', LEN(sn))

A more elegant solution uses negation twice. You can express the requirement by not allowing any character that isn't a digit:

sn NOT LIKE '%\[^0-9\]%'
Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish