But it's not missing. It's just … invisible, that's all.

Wed Mar 19, 2008 by Jeff Smith in t-sql

Microsoft Access has a pretty handy boolean property that you can set for any "text" column in your tables:

AllowZeroLength

True means that empty strings ('') are allowed in the column, False means that they are not.

This is actually quite nice, because by using this along with the Required (i.e., disallow NULLS) property, you can ensure that your column has an actual, non-Null, non-Empty String value without the need for any additional constraints.

SQL Server does not have this property, but we can easily achieve the same effect by using a CHECK constraint:

create table foo
(
column1 varchar(100) not null check (column1 <> '')
)

This enforces that column1 is not only never null, but it also cannot be an empty string. For example:

insert into foo (column1) values ('')

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "CK__foo__column1__6E8B6712". The conflict occurred in database "PlayGround", table "dbo.foo", column 'column1'.
The statement has been terminated.

However, we need to remember that there is another side effect of this check constraint: SQL Server ignores any trailing spaces when comparing two values, so this effectively also means that a non-empty string that consists of all space characters is also not valid in your table. To demonstrate:

insert into foo (column1) values (space(10))

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "CK__foo__column1__6E8B6712". The conflict occurred in database "PlayGround", table "dbo.foo", column 'column1'.
The statement has been terminated.

Now, this can be a good thing or a bad thing depending on what you are looking for. Overall, I'd say it's a good thing -- to me, a string of 30 spaces is just as bad as a NULL or an empty string in a column that is required, such as a project code or a first/last name. Of course, what ultimately is best for any specific situation depends on the requirements.

If you decide that you do want to disallow only empty strings but strings consisting of all spaces are valid, then you can write your check constraint like this:

create table foo
(
column1 varchar(100) not null check (column1 + 'x' <> 'x')
)

By appending 'x' to the end of the column's potential value and comparing the result with 'x', we are sure that if the column contains at least one space this check will be TRUE, but we are still not allowing empty strings.

So, don't forget about handling empty and/or "invisible" strings in your columns. It won't generally satisfy your client if you argue that "the username isn't missing -- it isn't null. It is just ... invisible!"

After all, you don't want to end with "invisible" payments on your invoices!

Legacy Comments


Chris 2008-03-20	re: But it's not missing. It's just ... invisible, that's all. column1 varchar(100) not null check (column1 + 'x' <> 'x') Not forgetting the trim function also: column1 varchar(100) not null check (rtrim(column1) <> '')

Jeff 2008-03-20	re: But it's not missing. It's just ... invisible, that's all. Hi Chris -- two things: 1) as mentioned, comparisons ignore trailing spaces. Using RTRIM() to trim trailing spaces is not necessary and has no effect and 2) the check constraint you wrote is not equivalent to the one you quoted -- they are completely different.

Chris McKenzie 2008-03-20	re: But it's not missing. It's just ... invisible, that's all. LEN() and DATALENGTH() might be a cleaner set of choices. To wit: DECLARE @foo TABLE ( column1 varchar(100) not null check (column1 <> '') ) insert into @foo (column1) values (' ') DECLARE @foo1 TABLE ( column1 varchar(100) not null check ( LEN(column1) <> 0) ) insert into @foo1 (column1) values (' ') DECLARE @foo2 TABLE ( column1 varchar(100) not null check ( DATALENGTH(column1) <> 0) ) insert into @foo2 (column1) values (' ') Nice post!

Manie Verster 2008-03-26	re: But it's not missing. It's just ... invisible, that's all. Hi Jeff, Nice post. I want to thank everybody on this and on other forums for sharing your knowledge. Sometimes I am about to go search for something to help me then I come upon my answer in this way. I am not quite new to SQL but mostly from a developers point of view. I like the idea of being a DBA and I am like a vacuum cleaner, sucking up all the knowledge I can get. Thanks again!!