How can I create a CHECK constraint for a VARCHAR column in MSSQL specifying a valid character set that can appear in data?

Question

I have a VARCHAR (30) column in a Microsoft SQL database representing the username. I would like to add a CHECK restriction, which allows you to use only a certain range of characters: in particular, az, AZ, underscore and dash. Which expression should be used?

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, you do not need both [az] and [az] .

Answer 2

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