September 9, 2014 at 6:59 pm
Hi,
I am sure that this is silly question, but i am confused about checking the empty string. please correct me what am doing wrong.
declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd
The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic. Any thoughts please
September 9, 2014 at 7:04 pm
The empty string ('') is NOT NULL so the first condition is true.
September 9, 2014 at 7:39 pm
To complement what David said, you need to use AND instead of OR.
Of course, you could go with the simple way of doing it.
declare @user varchar(30) = ''
IF @user > '' --This checks for not null and not empty
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
END
September 9, 2014 at 7:53 pm
Hi David,
Thanks for your reply.
Hi Luis,
Thanks for your solution and This is new syntax for me. this is the first time i am seeing this. Just curiosity how this works?
@user > ''
also if the > '' will work for integer or datetime datatype? Any suggestion and any example to check the integer/Datetime if possible
Thanks for your time on this gentle man.
September 9, 2014 at 7:59 pm
Are you seeing (ampersand)gt(semicolon)? That's a "greater than" sign showed like that by this site. You should use the normal sign in your query.
If you see the "greater than" sign, it works because no string can be lower than an empty string and null values will return "unknown" which isn't true.
September 9, 2014 at 10:51 pm
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.
-- Itzik Ben-Gan 2001
September 9, 2014 at 11:06 pm
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.
😎
September 10, 2014 at 5:36 am
thank you guys
September 10, 2014 at 6:36 am
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. Try this:
declare @user varchar(30)
set @user = char(15) + char(14) + char(16)
IF @user > '' --This checks for not null and not empty
BEGIN
select 'I am not empty'
END
ELSE
BEGIN
select 'I am empty'
END
select @user
If you want to make sure that your varchar value contains some thingelse than NULL and empty string use simple "NOT EQUAL" eg:
declare @user varchar(30)
set @user = char(15) + char(14) + char(16)
IF @user != '' --This checks for not null and not empty
BEGIN
select 'I am not empty'
END
ELSE
BEGIN
select 'I am empty'
END
select @user
September 10, 2014 at 6:39 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.
😎
What do you mean by UNKNOWN or MISSING value? There is a NULL one, and it is quite known 🙂
Check my previous post in this thread. There are some values "lower than" empty string/space.
September 10, 2014 at 2:11 pm
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).
September 10, 2014 at 2:23 pm
Eugene Elutin (9/10/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.
😎
What do you mean by UNKNOWN or MISSING value? There is a NULL one, and it is quite known 🙂
Check my previous post in this thread. There are some values "lower than" empty string/space.
Unfortunately there is no way of distinguishing between an unknown value or a missing value in standard sql, hence the NULL.
😎
September 10, 2014 at 5:40 pm
Alan.B (9/9/2014)
IF @user > '' --This checks for not null and not empty
Please don't do this... It's one of the things that drove me crazy with c programmers - the incessant urge to use side-effects as functionality.
Yes, it works and Yes, a 'good' programmer should understand it but...
IF ISNULL(@user, '') <> ''
works just as well, if a fraction of a second slower and is much easier to read and understand for both good and average programmers.
🙂
September 10, 2014 at 5:57 pm
SimonHolzman (9/10/2014)
Alan.B (9/9/2014)
IF @user > '' --This checks for not null and not emptyPlease don't do this... It's one of the things that drove me crazy with c programmers - the incessant urge to use side-effects as functionality.
Yes, it works and Yes, a 'good' programmer should understand it but...
IF ISNULL(@user, '') <> ''
works just as well, if a fraction of a second slower and is much easier to read and understand for both good and average programmers.
🙂
Hi Simon,
I have to disagree with you. I'm not using side effects, I'm using 3 value logic which is fundamental in SQL (in general, not only SQL Server). But most important, I'm using the comment to make it clear for anyone reading the code and not only "good" programmers.
The reason that I dislike your option is because someone will see that code and will include it in a WHERE clause making a query non-SARGable. It won't cause major issues on variables, but it can be a great penalty on columns.
I prefer a good commented code that will teach than a code that can be misused.
September 11, 2014 at 12:58 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.
😎
NULL is not the lowest possible value; it is unknown. So, in case @user is null:
IF @user > '' -- false
IF @user < '' -- false too
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply