Check the Variable is Empty or Null

  • Also note empty string may be rather lengthy 🙂

    declare @user varchar(30)

    set @user = char(0) + char(0) + char(0)

    IF isnull (@user,'') != '' -- also try other discussed checks

    BEGIN

    select 'I am not empty', len(@user)

    END

    ELSE

    BEGIN

    select 'I am empty', len(@user)

    END

  • Eugene Elutin (9/10/2014)


    You may do it... sometimes...

    But it is NOT right. Try this:

    .......

    I find that if I stick <shift in>, <shift out>, and <data link escape> into a string it compares greater than the empty string. That is true because every non-empty string compares greater than the empty string.

    Do you have an example that doesn't? You can even suggest a collation to use to make a non-empty tring not compare greater than the empty one.

    Tom

  • Luis Cazares (9/10/2014)


    Eugene Elutin (9/10/2014)


    Alan.B (9/9/2014)


    IF @user > '' --This checks for not null and not empty

    Cool. I never knew you could do that, very interesting :laugh:

    Edit: sql code tag messed up my "greater than" symbol.

    You may do it... sometimes...

    But it is NOT right.

    That's just nonsense, but not from you Eugene. It's something illogical from SQL Server. It's good to know, even if I don't expect to use those characters (one can never be too cautious when coding).

    I don't think SQL Server does that. At least I can't get it to.

    Tom

  • Eirikur Eiriksson (9/9/2014)


    Alan.B (9/9/2014)


    IF @user > '' --This checks for not null and not empty

    Cool. I never knew you could do that, very interesting :laugh:

    Edit: sql code tag messed up my "greater than" symbol.

    This is because in SQL Server, the unknown/missing/null is regarded as the lowest possible value.

    😎

    For the purpose of boolean expressions using comparison, null is not comarable - it is not lower than other values, nor higher, nor equal, nor unequal, all comparisons return the truth value UNKNOWN.

    For the purpose of ordering keys in an index, NULL does compare lower than anything else; but that has no relevance to "IF @user > '' ".

    Tom

  • F. van Ruyven (9/11/2014)


    Eirikur Eiriksson (9/9/2014)


    Alan.B (9/9/2014)


    IF @user > '' --This checks for not null and not empty

    Cool. I never knew you could do that, very interesting :laugh:

    Edit: sql code tag messed up my "greater than" symbol.

    This is because in SQL Server, the unknown/missing/null is regarded as the lowest possible value.

    😎

    NULL is not the lowest possible value; it is unknown. So, in case @user is null:

    IF @user > '' -- false

    IF @user < '' -- false too

    Doesn't change the fact that SQL Server considders NULL being the lowest possible value.

    😎

  • Eirikur Eiriksson (9/11/2014)


    F. van Ruyven (9/11/2014)


    Eirikur Eiriksson (9/9/2014)


    Alan.B (9/9/2014)


    IF @user > '' --This checks for not null and not empty

    Cool. I never knew you could do that, very interesting :laugh:

    Edit: sql code tag messed up my "greater than" symbol.

    This is because in SQL Server, the unknown/missing/null is regarded as the lowest possible value.

    😎

    NULL is not the lowest possible value; it is unknown. So, in case @user is null:

    IF @user > '' -- false

    IF @user < '' -- false too

    Doesn't change the fact that SQL Server considders NULL being the lowest possible value.

    😎

    SELECT CASE WHEN NULL < 'A' THEN 'NULL is lower than A - it is a FACT!' ELSE 'What a bullsh*t!' END

    and just to nail it:

    SELECT CASE WHEN NULL > 'A' THEN 'NULL is bigger than A it is a FACT!' ELSE 'What a bullsh*t!' END

    SELECT CASE WHEN NULL = 'A' THEN 'NULL is equal to A it is a FACT!' ELSE 'What a bullsh*t!' END

    SELECT CASE WHEN NULL != 'A' THEN 'NULL is not equal to A it is a FACT!' ELSE 'What a bullsh*t!' END

    SELECT CASE WHEN NULL = NULL THEN 'NULL is equal to NULL - it is for sure!' ELSE 'What a bullsh*t!' END

    SELECT CASE WHEN NULL IS NULL THEN 'That is the ony a case: NULL is NULL' ELSE 'You want see that one!' END

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • DECLARE @test-2VARCHAR(10)

    SET @test-2 = NULL

    IF @test-2 > ''

    PRINT '>'

    ELSE IF @test-2 = ''

    PRINT '='

    ELSE IF @test-2 < ''

    PRINT '<'

    ELSE

    PRINT 'Bloody NULLs'

    I get;

    Bloody NULLs

  • You have all been really helpful about this... I have had a few attempts at replying and trying to thank people for their input but my writing has been too lengthy and keeps timing out ! <TL;DR> 🙂

    Whatever approach is used, including comments is helpful. Part of my gripe with the use of the side effect, as I phrased it, is that my experience with people who like using that type of code is that they also tend to avoid using comments either because they feel that their code is so straightforward that comments are unnecessary or because they feel that "real" programmers Don't Need No Stinkin' Comments...

    The performance impact of using ISNULL in WHERE clauses is something I will need to investigate further. In practice, I usually use it to exclude data - ie: ISNULL(Gender, 'X') <> 'M' - or to explicitly state an assumption - ie: ISNULL(Delete_Ind, 'N') = 'Y' - so I will need to see what other options are available that will perform better.

    I have some mixed feelings with regard to data validation... on the one hand, I really like my Database to have reliable, consistent, valid data. On the other hand, the users are not always able to provide that and it gets very frustrating when they cannot add basic information for a new customer because the system requires a gender and they only have a name (they end up guessing the gender and then forgetting to check whether they guessed right when the customer actually arrives). Yes, it SHOULD be designed better, but "Should" and "Is" are not the same thing. (I know that allowing NULL as a gender is arguably worse than having a few women marked as Male or vice versa but at least with the NULLs I know that the gender is unknown. When it says Male, I have no way to know that the gender was a guess).

    I like NULLs... they have a very clear value to me... "Dunno". This isn't even "Unknown". Heck, we could have customers with U (for Unknown) as their gender as distinct from those with NULL... U would mean that the customer themselves did not know the answer (we live in unusual times) while NULL means that the system does not know. (FWIW, we also have O for Other and N for None as entries for the Sex field in our system... Oh for the days when the answer was "Yes, Please")

  • SimonHolzman (9/11/2014)


    DECLARE @test-2VARCHAR(10)

    SET @test-2 = NULL

    IF @test-2 > ''

    PRINT '>'

    ELSE IF @test-2 = ''

    PRINT '='

    ELSE IF @test-2 < ''

    PRINT '<'

    ELSE

    PRINT 'Bloody NULLs'

    I get;

    Bloody NULLs

    Not that we need to reopen old wounds, but NULL isn't a value - it's a state representing the lack of a value. Thus it's not comparable (i.e. any of those compare options will yield "unknown"). The ELSE fires because none of the other parts evaluate to TRUE.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (#4) (9/11/2014)


    Not that we need to reopen old wounds, but NULL isn't a value - it's a state representing the lack of a value. Thus it's not comparable (i.e. any of those compare options will yield "unknown"). The ELSE fires because none of the other parts evaluate to TRUE.

    I know, the point I was making was that conflating the NULL checking with the Blank checking can cause issues, which is why I dislike the original

    IF @Field > ''

    PRINT 'Not Blank'

    ELSE

    PRINT 'Blank'

    code. It does work, but it makes an assumption that implies that this code will also work;

    IF @Field <= ''

    PRINT 'Blank'

    ELSE

    PRINT 'Not Blank'

    Including the comment that explains why the first snippet is being used does make it more acceptable to me, but I still dislike the use of the assumption. Then again, I do not explicitly TRIM fields to ensure that they are zero length, so I admit my hypocrisy here !

  • Thanks Simon - and agreed.

    I quoted your snippet because it was a good test that highlights that those logical criteria most of the time will return TRUE or FALSE, but can also return UNKNOWN. Just because the ELSE clause fires does NOT mean it's FALSE, it just means something other than explicitly TRUE.

    And definitely - I'd be highlighting the issue with comments to remind those that follow behind me that it could happen.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Eugene Elutin (9/11/2014)


    Eirikur Eiriksson (9/11/2014)


    F. van Ruyven (9/11/2014)


    Eirikur Eiriksson (9/9/2014)


    Alan.B (9/9/2014)


    IF @user > '' --This checks for not null and not empty

    Cool. I never knew you could do that, very interesting :laugh:

    Edit: sql code tag messed up my "greater than" symbol.

    This is because in SQL Server, the unknown/missing/null is regarded as the lowest possible value.

    😎

    NULL is not the lowest possible value; it is unknown. So, in case @user is null:

    IF @user > '' -- false

    IF @user < '' -- false too

    Doesn't change the fact that SQL Server considders NULL being the lowest possible value.

    😎

    SELECT CASE WHEN NULL < 'A' THEN 'NULL is lower than A - it is a FACT!' ELSE 'What a bullsh*t!' END

    and just to nail it:

    SELECT CASE WHEN NULL > 'A' THEN 'NULL is bigger than A it is a FACT!' ELSE 'What a bullsh*t!' END

    SELECT CASE WHEN NULL = 'A' THEN 'NULL is equal to A it is a FACT!' ELSE 'What a bullsh*t!' END

    SELECT CASE WHEN NULL != 'A' THEN 'NULL is not equal to A it is a FACT!' ELSE 'What a bullsh*t!' END

    SELECT CASE WHEN NULL = NULL THEN 'NULL is equal to NULL - it is for sure!' ELSE 'What a bullsh*t!' END

    SELECT CASE WHEN NULL IS NULL THEN 'That is the ony a case: NULL is NULL' ELSE 'You want see that one!' END

    You are still not getting it are you? I'm trying to point out a discrepancy in the way SQL Server handles NULL, a simple example would be

    DECLARE @NULLS TABLE

    (

    NID INT IDENTITY (1,1) NOT NULL

    ,VAL VARCHAR(20) NULL

    );

    INSERT INTO @NULLS (VAL)

    VALUES ('1'),(NULL),(NULL),('4'),(NULL),('5'),(NULL),('7'),('8');

    SELECT

    N.NID

    ,ISNULL(N.VAL,'Mind your language!') AS VAL

    FROM @NULLS N

    ORDER BY N.VAL ASC

    Which returns

    NID VAL

    ----------- --------------------

    2 Mind your language!

    3 Mind your language!

    5 Mind your language!

    7 Mind your language!

    1 1

    4 4

    6 5

    8 7

    9 8

    Now we change the sort order to DESC

    DECLARE @NULLS TABLE

    (

    NID INT IDENTITY (1,1) NOT NULL

    ,VAL VARCHAR(20) NULL

    );

    INSERT INTO @NULLS (VAL)

    VALUES ('1'),(NULL),(NULL),('4'),(NULL),('5'),(NULL),('7'),('8');

    SELECT

    N.NID

    ,ISNULL(N.VAL,'Mind your language!') AS VAL

    FROM @NULLS N

    ORDER BY N.VAL DESC

    and surprisingly we get

    NID VAL

    ----------- --------------------

    9 8

    8 7

    6 5

    4 4

    1 1

    2 Mind your language!

    3 Mind your language!

    5 Mind your language!

    7 Mind your language!

    Lets add some spanners into the mix

    DECLARE @NULLS TABLE

    (

    NID INT IDENTITY (1,1) NOT NULL

    ,VAL VARCHAR(20) NULL

    );

    INSERT INTO @NULLS (VAL)

    VALUES ('1'),(CHAR(0)),(''),('4'),(NULL),('5'),(CHAR(32)),('7'),('-9999999999999998');

    SELECT

    N.NID

    ,ISNULL(N.VAL,'Mind your language!') AS VAL

    FROM @NULLS N

    ORDER BY N.VAL DESC

    Which returns

    NID VAL

    ----------- --------------------

    9 -9999999999999998

    8 7

    6 5

    4 4

    1 1

    2

    3

    7

    5 Mind your language!

  • Eirikur Eiriksson (9/11/2014)


    Lets add some spanners into the mix

    Although the first two chunks indicate clearly that NULL is ordered low by ORDER BY, they don't indicate anything else.

    The "spanners" added for the third chunk are nothing to do with (T-)SQL but with the presentation chosen by SSMS. The strings char(32), char(0), and '' are all given the same visible presentation form (absence of any visible character). That tells us absolutely nothing new about ordering of strings including nulls.

    Tom

  • Although the first two chunks indicate clearly that NULL is ordered low by ORDER BY, they don't indicate anything else.

    Exactly. Remember Oracle's ORDER BY ... NULLS FIRST.

    ORDER BY must place NULLs somewhere and it's just a convention where to place them. It has nothing to do with comparing ('>' ,'<') value and NULL.

  • ...

    You are still not getting it are you? I'm trying to point out a discrepancy in the way SQL Server handles NULL, a simple example would be

    ...

    As a SQL Server beginer, using it just since last Monday...

    No, I'm really not getting it.

    What kind of discrepency in handling NULL valus by SQLServer are you talking about?

    You have stated "...the fact that SQL Server considders NULL being the lowest possible value.", but simple comparison with any values proved that is not a "lowest possible" value. And my 5 days experience suggets it is simply because NULL is NOT a value at all (as someone already pointed it in the thread) but a state indicating that there is no value here.

    Providing an example of how SQL Server orders data and places NULL at the top, doesn't make any difference to the above fact and doesn't constitue any discrepency in handling it by SQLServer

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 15 posts - 16 through 30 (of 38 total)

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