Systems Engineering and RDBMS

Data Issues due to CHAR(0)

Posted by decipherinfosys on July 12, 2007

At one of the client sites (they use SQL Server as their RDBMS), in one of the columns that was declared as a variable character length column, the client application was dumping char(0) into the field whenever they did not know the value of the column.  This column was a mandatory column and because of bad programming, instead of doing validation checks and preventing the bad data from getting into the column, the developer had coded it to put in CHAR(0) instead.  What he really meant to do was to enter an empty string – even that is very bad practice – you can read more on that on our blog post here.

CHAR(0) is the NUL character (notice the missing L).  Just as you signal the end of input with the EOF constant, you signal the end of a string with the NUL character.  If all you are looking for is entering a space, use CHAR(32).  If the column had an empty string “embedded” in the description, that equates to CHAR(32) (Ascii value of 32 is space) and not CHAR(0).  Here is an example:

declare @table table (col1 varchar(30) not null)
insert into @table values (char(0))
insert into @table values (‘abcd’)

select count(1) from @table where col1 is null
———–
0

(1 row(s) affected)

select count(1) from @table where col1 = ”
———–
0

(1 row(s) affected)

select count(1) from @table where col1 = char(0)
———–
1

(1 row(s) affected)

select count(1) from @table where col1 is not null

———–
2

(1 row(s) affected)

This was fixed in the code and the existing data was put through the validation routine to help clean up the data.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: