The SQL-92 standard makes a distinction between empty string and NULL. It is Oracle that considers empty string and NULL to be equivalent. My impression is that Sybase and old versions of SQL Server behave the same way.
| [reply] |
Sybase (and presumably MS-SQL) will silently convert "" to " " as it can't store a zero-length string that is not NULL.
Michael
| [reply] |
I can't check it at this moment, but I don't think any recent MS SQL (that is 7.0 and up) does this for varchars. I'm sure I would notice. It does change an empty string to a string containing the right number of spaces for char(N) columns, but there it can't do anything better. If I still remember this node tomorrow when I am at work and can try things I'll test it and post an update.
Update: Yep, I was right:
create table foo (svarchar varchar(10), schar char(10))
insert into foo (svarchar, schar) values ('', '')
insert into foo (schar) values ('')
select '>' + svarchar + '<', '>' + schar + '<'
from foo
Jenda
Always code as if the guy who ends up maintaining your code
will be a violent psychopath who knows where you live.
-- Rick Osborne
| [reply] [d/l] |
> Now, I know there's a difference between "" and undef in Perl, just like I know there's a difference between "" and NULL in my database.
You do?
Not if your database follows the SQL-92 standard!
Leaving aside the issue of what the SQL-92 standard actually says, and also leaving aside the holy war about whether MySQL is a real database or not, the fact remains that my database, which is MySQL, does treat "" and NULL as different, so my above statement is correct.
In the meantime, I've decided to use a different input validator, which is working much better for me.
| [reply] |