Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
SQL Server disregards trailing spaces, which is why a single space would still violate the constraint. – JNK Dec 21 '11 at 15:31
up vote 8 down vote accepted

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

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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