1

I have a column with min length of 2 characters. I added a constraint. However, spaces are allowed according to the requirement. SQL Server does not let me insert spaces with this constraint.

I tried DATALENGTH as a constraint instead of LEN but I think DATALENGTH is Oracle syntax.

create table temp (field varchar(10));

ALTER TABLE temp WITH CHECK 
    ADD CONSTRAINT [CK_FIELD_MIN_LENGTH_MIN_2] CHECK ((len([field])>=(2)))

When I try to insert a space I get an error.

INSERT INTO temp values ('  ');

Error: The INSERT statement conflicted with the CHECK constraint "CK_FIELD_MIN_LENGTH_MIN_2". The conflict occurred in database "EDIX125010", table "dbo.temp", column 'field'.

How can I satisfy the requirement of the minimum number of characters and also allowing spaces?

I don't believe this is a duplicate because the other examples are for SELECT statements (attempting to retrieve data). My question is about INSERT INTO.

Michael Kotoyan
  • 21
  • 4
  • DATALENGTH is a function of MSSQL. DATALENGTH(' ') = 2 – Ross Bush Dec 21 '18 at 18:48
  • 1
    Regardless of whether it's a `SELECT` or an `INSERT`, I think you've missed the point of the answer on the duplicate question, which is to use `DATALENGTH` instead of `LEN`. – Joe Stefanelli Dec 21 '18 at 19:05
  • @Ross Bush - Thanks so much. DATALENGTH Worked. I revised my statement and it looks like this. ALTER TABLE temp WITH CHECK ADD CONSTRAINT [CK_FIELD_MIN_LENGTH_MIN_2] CHECK ((DATALENGTH([field])>=(2))) – Michael Kotoyan Dec 21 '18 at 22:58

0 Answers0