CODEWITHSUNDEEP
sqlsql-server-2008nullnull-check
Bharath
Bharath

Reputation: 581

Null or empty check for a string variable

if((isnull(@value,''))='')

I want to know whether the above piece of code works in checking if the variable is null or empty.

Upvotes: 25

Views: 177053

Answers (9)

Sergey Zuyev
Sergey Zuyev

Reputation: 11

    IF (LEN(@value) > 0) PRINT 'Variable is not null and not empty'

Upvotes: 1

Sérgio Roberto Penha Heredia
Sérgio Roberto Penha Heredia

Reputation: 1

declare @sexo as char(1)

select @sexo='F'

select * from pessoa

where isnull(Sexo,0) =isnull(@Sexo,0)

Upvotes: 0

Ajay Jamwal
Ajay Jamwal

Reputation: 39

You can try this.....

DECLARE @value Varchar(100)=NULL
IF(@value = '' OR @value IS NULL)
  BEGIN
    select 1
  END
ELSE
  BEGIN
    select 0
  END

Upvotes: 0

Fahad Akbar
Fahad Akbar

Reputation: 1

You can try

<column_name> is null

in the where clause.

Upvotes: 0

milan
milan

Reputation: 11

Try this:

ISNULL(IIF (ColunmValue!='',ColunmValue, 'no units exists') , 'no units exists') AS 'ColunmValueName'

Upvotes: 1

AnasChavadi
AnasChavadi

Reputation: 427

Use This way is Better 

if LEN(ISNULL(@Value,''))=0

This check the field is empty or NULL

Upvotes: 11

bvr
bvr

Reputation: 4826

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

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460028

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

DEMO

Upvotes: 9

Guffa
Guffa

Reputation: 700152

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 ''.

Upvotes: 45

Related Questions