Post reply

empty string INSERTs as 0 to INT column - how to prevent it?

  • March 19, 2014 at 1:10 pm

    #283641

    While coding in Powershell, due to happenstance, an update was sent to SQL Server that referenced an empty string ('') instead of an INT value.

    There was no constraint or trigger on the table, and to my surprise, the value in the column became 0.

    Simple repro:

    CREATE TABLE dbo.t1 (

    col1 INT NOT NULL --CHECK (col1 BETWEEN 0 AND 4)

    )

    INSERT dbo.t1 (col1) VALUES ('')

    SELECT * FROM dbo.t1

    UPDATE dbo.t1 SET col1 = 1

    SELECT * FROM dbo.t1

    UPDATE dbo.t1 SET col1 = ''

    SELECT * FROM dbo.t1

    I have tried every type of check constraint and/or trigger that I can think of, and nothing works. Running SELECT * FROM INSERTED in the body of the trigger displays 0, so it seems that you can't catch it there.

    Un-commenting the check constraint above also does not catch it.

    Can anyone think of a way to prevent this?

    Thanks in advance --

    SQLNYC

  • March 19, 2014 at 1:18 pm

    #1698503

    That's an implicit conversion. You can check this:

    SELECT CAST('' AS int), CAST('' AS datetime), CAST('' AS bit)

    The question is what do you want instead of the zero? you can't have an empty string in an int column. You could have a zero or a NULL value, both values are different. To get a NULL value, you could use NULLIF().

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 19, 2014 at 1:41 pm

    #1698511

    Hi Luis,

    Thanks very much for your reply.

    I want it to fail if it's an empty string. Your suggestion of NULLIF() worked.

    Much appreciated.

    SQLNYC

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply