Check constraint to prevent insertion of invalid characters.

,

A column level check constraint may be used to prevent insertion of data containing characters within a specific ASCII range. In the supplied example, we are preventing insertion of data containing character within the range of char(0) - char(31). For example, it will reject data containing embedded tabs {9}, carriage returns {13}, or line feeds {0}. It is reccomended that you provide the constraint with a descriptive name, because it will assist users with identifying the source of the error.
Here is the result of inserting data containing a trailing tab.
insert into MySample ( col1 )
  values ('John'+char(9));
Msg 547, Level 16, State 0, Line 20
The INSERT statement conflicted with the CHECK constraint "cc_col1_InvalidData". 
The conflict occurred in database "master", table "dbo.MySample", column 'col1'.
create table MySample
(
  col1 varchar(30) null 
    constraint cc_col1_InvalidData check 
    (case when col1 like '%['+char(0)+'-'+char(31)+']%' 
    then 0 
    else 1 
    end = 1)
);

Rate

4.33 (6)

Share

Share

Rate

4.33 (6)