February 5, 2004 at 9:51 am
Hi evreryone,
is there a setting on SQL Server to allow inserting empty string result in NULL in the DB?
Example:
Create table myTable (c1 varchar(10))
insert into myTable values ('')
select count(*) from myTable where c1 is null
Result:
-----------
1
(1 row(s) affected)
For the moment the result is:
-----------
0
(1 row(s) affected)
Thank's
Carl
February 5, 2004 at 10:28 am
'' isn't null.
insert mytable select null
will do it.
February 5, 2004 at 10:35 am
Hello Steve,
In the SQL Server world '' is not NULL but in the Oracle world it is.
I try to find a way to make them behaves the same.
For us it is the rigth behavior to have '' being NULL.
No settings to do that?
Best Regards,
Carl
February 5, 2004 at 12:59 pm
I don't know the world Oracle exists in, but here in SQL Server land (and the rest of those RDBMS out there) NULL is not the same as an empty string.
Sorry, no setting that I know of.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 5, 2004 at 1:05 pm
Ok thank's Frank,
Bye the way my english is not very good so the expression "Oracle world" should not be mis-interpreted.
Best regards,
Carl
February 5, 2004 at 1:10 pm
Sorry, no harm meant!
More of a little after work joking, as here in my place it is already 9 PM.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 6, 2004 at 5:43 am
If you create your table using column defaults of '':
CREATE TABLE [myTable] (
[column1] [char] (10) DEFAULT (''),
[column2] [char] (10) DEFAULT ('')
) ON [PRIMARY]
GO
any insert that would produce a null value will instead contain ''.
RobR
credo quia absurdum est
February 6, 2004 at 6:07 am
Sure this will work, but still NULL != empty string.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 6, 2004 at 10:44 am
Yes, Oracle does treat empty strings as NULL, and no SQL Server does not. There are two options I can think of:
1. Create a trigger on the table(s) in question to check for the empty string condition and change it to a NULL
2. Use a CASE statement in your select to change the empty string into a NULL.
The other option is to fix the application so that instead of empty strings, it passes NULL.
OK, so that was three options... I always try to "Under promise and over deliver"
/*****************
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
*****************/
February 6, 2004 at 10:52 am
Thanks for your suggestions dcpeterson but we don't want to add an overhead.
I would have thought that a setting would have made it possible to modify this behavior at the database server level.
Best regards,
Carl
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy