July 2, 2009 at 12:39 pm
All,
How painful it is when you get what you think is a simple question and it stumps you. So I throw it out to you all.
Here is what I got
Create Table #MyTable
(ColA Char(5) ,
ColB Char(5) NOT NULL )
Insert #MyTable Values ('ABCDE', NULL)
== This fails . . . . all is right with the universe
Insert #MyTable Values ('ABCDE','')
It loaded the data.
Now the Length of COLB is 0 (Zero)
So what is there? It's not NULL.
Confused in Florida . . . . Again.
PS Happy 4th of July for those of you in the States.
July 2, 2009 at 12:50 pm
it is a blank which can be test as fieldname = '' < that is 2 single quotes.
If you absolutely don't want that to happen you could add a check contraint on that column that says the fieldname cannot be blank, since it is defined as NULLable you could still have that..
Blank and NULL are very different. A NULL is usually (my way of thinking) an I don't know condition, whereas a blank is, I know what it is, its a blank. I'm not sure if that is clear..
CEWII
July 2, 2009 at 2:00 pm
NULL is undefined. It's the absence of a value. A blank is a value, what it might mean and when it is acceptable is entirely up to the database designer. The use of blanks is generally not a good way to get around a NOT NULL constraint, but it is a way to do it.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 2, 2009 at 2:32 pm
douglascfast
Try executing this command:
SELECT ASCII(SUBSTRING(ColB,1,1))AS 'Character Code', DATALENGTH(ColB) AS 'Length' FROM #MyTable
The character code for a blank character is an integer 32. Note that what you are really attempting to measure is the DATALENGTH of the column which should give you a value of 5 -that is the column contains 5 characters which in your case are all blank characters.
For a listing of the ASCII character set try this site:
http://www.cdrummond.qc.ca/cegep/informat/Professeurs/Alain/files/ascii.htm
Hopes this clears up the muddy waters somewhat.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply