How can I create a CHECK constraint on a VARCHAR column in MSSQL specifying a valid set of characters that may appear in the data?

Question

I have a VARCHAR(30) column in a Microsoft SQL database representing a username. I'd like to add a CHECK constraint that allows only a certain range of characters to be used: specifically, a-z, A-Z, underscore and dash. What expression must I use?

Answer 1

create table t (
   a varchar(30) check (
      a like replicate('[a-zA-Z\_-]', len(a)) escape '\'));

If your collation is not case sensitive then you don't need both [a-z] and [A-Z].

Answer 2

CREATE TABLE T 
(
 a VARCHAR(30) NOT NULL UNIQUE
    CHECK (a NOT LIKE '%[^a-zA-Z\_-]%' ESCAPE '\')
);