Post reply

Strange isnull \ nullif result

  • July 11, 2009 at 4:36 am

    #202237

    Hiya,

    Either I am overtired and missing something simple, or there is a problem using isnull \ nullif combination.

    If I run this:

    select isnull(nullif('', ''), 'abcd')

    I get the result 'a', whearas I would expect to get 'abcd'.

    Version is Microsoft SQL Server 2005 - 9.00.3077.00 (Intel X86)

    Thanks,

    Martin

  • July 11, 2009 at 4:40 am

    #1023471

    Seems that SQL Server determines your data type by first string value '' and selects (VAR)CHAR(1)

    This works:

    SELECT ISNULL(NULLIF(CONVERT(VARCHAR(10), ''), ''), 'abcd')

  • July 11, 2009 at 5:19 am

    #1023480

    Another reason to use COALESCE instead.

    N 56°04'39.16"
    E 12°55'05.25"

  • July 11, 2009 at 7:05 am

    #1023487

    Thanks for the responses, guys. I can get around the problem easily enough, but I was really just curious to know if anybody had any theories as to why this is returning just the one character. I've used this before and it's worked fine.

  • July 11, 2009 at 8:42 am

    #1023496

    As I told you, the length of the returned data depends on the length of the input text.

    Try this:

    DECLARE @v1 VARCHAR(1)

    DECLARE @v2 VARCHAR(2)

    DECLARE @v3 VARCHAR(3)

    DECLARE @v4 VARCHAR(4)

    SELECT ISNULL(NULLIF(@v1, ''), 'abcd')

    SELECT ISNULL(NULLIF(@v2, ''), 'abcd')

    SELECT ISNULL(NULLIF(@v3, ''), 'abcd')

    SELECT ISNULL(NULLIF(@v4, ''), 'abcd')

  • July 11, 2009 at 9:01 am

    #1023504

    Ahh, I see now. So, although the nullif() function is returning a NULL value, it is a NULL value with a datatype of varchar(1). I always was more awake in the afternoons!

    Thanks Florian.

  • July 11, 2009 at 9:07 am

    #1023508

    Glad we could help 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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