September 11, 2014 at 7:08 am
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
September 11, 2014 at 9:22 am
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
September 11, 2014 at 9:28 am
Luis Cazares (9/10/2014)
Eugene Elutin (9/10/2014)
Alan.B (9/9/2014)
IF @user > '' --This checks for not null and not emptyCool. 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
September 11, 2014 at 9:32 am
Eirikur Eiriksson (9/9/2014)
Alan.B (9/9/2014)
IF @user > '' --This checks for not null and not emptyCool. 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
September 11, 2014 at 9:36 am
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 emptyCool. 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.
😎
September 11, 2014 at 11:22 am
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 emptyCool. 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
September 11, 2014 at 11:53 am
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")
September 11, 2014 at 11:54 am
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?
September 11, 2014 at 12:08 pm
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 !
September 11, 2014 at 12:40 pm
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?
September 11, 2014 at 1:14 pm
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 emptyCool. 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!' ENDSELECT 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!
September 11, 2014 at 2:04 pm
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
September 12, 2014 at 1:57 am
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.
September 12, 2014 at 2:09 am
...
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
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply