Post reply

NULL vs

  • February 5, 2004 at 9:51 am

    #65028

    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

    #492931

    '' isn't null.

     

    insert mytable select null

    will do it.

  • February 5, 2004 at 10:35 am

    #492933

    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

    #492953

    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

    #492954

    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

    #492955

    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

    #493034

    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

    #493044

    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

    #493132

    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

    #493133

    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