if((isnull(@value,''))='')
I want to know whether the above piece of code works in checking if the variable is null or empty.
9 Answers
Yes, that code does exactly that.
You can also use:
if (@value is null or @value = '')
Edit:
With the added information that @value is an int value, you need instead:
if (@value is null)
An int value can never contain the value ''.
3 Comments
Eduardo La Hoz Miranda
Also important to know that you should stick to using "is null " or "is not null" when dealing with comparisons that may or not be null.
rivaldid
what about if (LEN(@value) is null) when @value is string?
Guffa
@rivaldid: That's the same thing as checking if the value is null. The length of a string is only null when the string is null. If the string has a zero length, the
len function returns zero, not null.Use This way is Better
if LEN(ISNULL(@Value,''))=0
This check the field is empty or NULL
1 Comment
ESP
I like this but modified it: if ''=ISNULL(@Value,'')
Yes, you could also use COALESCE(@value,'')='' which is based on the ANSI SQL standard:
SELECT CASE WHEN COALESCE(@value,'')=''
THEN 'Yes, it is null or empty' ELSE 'No, not null or empty'
END AS IsNullOrEmpty
Comments
Yes, it works. Check the below example. Assuming @value is not int
WITH CTE
AS
(
SELECT NULL AS test
UNION
SELECT '' AS test
UNION
SELECT '123' AS test
)
SELECT
CASE WHEN isnull(test,'')='' THEN 'empty' ELSE test END AS IS_EMPTY
FROM CTE
Result :
IS_EMPTY
--------
empty
empty
123
Comments
Try this:
ISNULL(IIF (ColunmValue!='',ColunmValue, 'no units exists') , 'no units exists') AS 'ColunmValueName'
Comments
IF (LEN(@value) > 0) PRINT 'Variable is not null and not empty'
Comments
You can try
<column_name> is null
in the where clause.
1 Comment
Martin Davies
Your answer could be made more to the person asking the question if you add more detail.
You can try this.....
DECLARE @value Varchar(100)=NULL
IF(@value = '' OR @value IS NULL)
BEGIN
select 1
END
ELSE
BEGIN
select 0
END
Comments
declare @sexo as char(1)
select @sexo='F'
select * from pessoa
where isnull(Sexo,0) =isnull(@Sexo,0)
@value.INT, so no need to check for that case if the type is reallyINT.