CODEWITHSUNDEEP
sqlt-sqlsql-server-expresscheck-constraints
Jed
Jed

Reputation: 10887

How to create a concise SQL 'Check Constraint' that disallows certain chars for all string fields?

I have a SQL (SQL Server Express 2008) Table that contains a handful of VARCHAR fields.

I would like to create a Check Constraint which will make sure that none of the VARCHAR values contain certain characters.

Example: (Do not allow <, >, or ? characters in the FirstName and LastName columns)

(NOT [FirstName] LIKE '%<%' 
AND NOT [FirstName] LIKE '%>%' 
AND NOT [FirstName] LIKE '%?%')
AND 
(NOT [LastName] LIKE '%<%' 
AND NOT [LastName] LIKE '%>%' 
AND NOT [LastName] LIKE '%?%')

The SQL syntax above works fine, but it would be nice if there was shorthand way of doing the same thing. Notice the redundancy in the example. This is cumbersome if I want to add more columns and/or invalid characters

It would be nice if we could do something like this:

NOT FirstName,LastName LIKE IN ('<','>','?')

Is it possible for me to do something like this in a Check Constraint expression?

Upvotes: 2

Views: 4237

Answers (2)

ismail
ismail

Reputation: 11

alter table nom_table, you should add constraint ck check(firstname not like '%[<,>,?]%').

Upvotes: 1

GilM
GilM

Reputation: 3761

You can use

NOT ([FirstName]  LIKE '%[<>?]%' OR [LastName]  LIKE '%[<>?]%')

Upvotes: 5

Related Questions