On SQL Server not nullable varchar columns can hold empty strings.

Under ORACLE they can't, because varchar2 treat '' as equivalent to NULL.

If you design a database schema suitable for both RDBMS it seems to be a good idea to add to each not nullable varchar column on SQL Server a constraint which disallows empty strings.

But which is the best way to formulate such a constraint?

I started with

if OBJECT_ID('varchar_without_empty_cols') > 0 drop table varchar_without_empty_cols

create table varchar_without_empty_cols (
id int ,
val varchar(10) not null CHECK (val <> '') 

insert into varchar_without_empty_cols values (1, ' ')  -- this ought be OK
insert into varchar_without_empty_cols values (2, '')   -- this has to violate the check
insert into varchar_without_empty_cols values (3, null) -- this violates the not null

select * from varchar_without_empty_cols

But this constraint not only inhibits empty strings, which is intended, but it also inhibits strings consisting of a single character, and that is not what I intend.

  • 3
    SQL Server disregards trailing spaces, which is why a single space would still violate the constraint.
    – JNK
    Dec 21, 2011 at 15:31

1 Answer 1


To allow strings containing only spaces but disallow empty strings you can use

CREATE TABLE varchar_without_empty_cols
     id  INT,
  • +1 But I'm curious if there other ways to write this constraint.
    – bernd_k
    Dec 21, 2011 at 15:37
  • 2
    @bernd_k - CHECK (val LIKE '_%' ) Dec 21, 2011 at 15:39
  • It seems that both Oracle and SQL-Server need special treatment to identify empty strings. Dec 21, 2011 at 15:48
  • @Martin: I suppose that CHECK (val > '' ) would not work either in SQL-Server. Dec 21, 2011 at 15:56
  • 1
    @ypercube CHECK (val > '' ) doesn't work in SQL-Server.
    – bernd_k
    Dec 21, 2011 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.