Null or empty check for a string variable

Question

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

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

its a INT type. i want to consider 0 as valid.. want to eliminate only NULL and '' values — Bharath, Apr 18 '13 at 10:16
@Bharath '' is not a valid value for an `INT`, so no need to check for that case if the type is really `INT`. — Joachim Isaksson, Apr 18 '13 at 10:18

score 44 · Accepted Answer · answered Apr 18 '13 at 10:15

44

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

answered Apr 18 '13 at 10:15

Guffa

687,336
108
737
1,005

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. – Eduardo La Hoz Miranda Jun 08 '15 at 21:07
what about if (LEN(@value) is null) when @value is string? – rivaldid May 28 '21 at 18:59
@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. – Guffa Jul 05 '21 at 10:45

score 11 · Answer 2 · edited May 12 '14 at 06:41

11

Use This way is Better

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

This check the field is empty or NULL

edited May 12 '14 at 06:41

Gwenc37

2,064
7
18
22

answered May 12 '14 at 06:11

AnasChavadi

427
1
4
10

2

I like this but modified it: if ''=ISNULL(@Value,'') – ESP Feb 18 '16 at 15:14

score 9 · Answer 3 · answered Apr 18 '13 at 10:16

9

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

answered Apr 18 '13 at 10:16

Tim Schmelter

450,073
74
686
939

score 1 · Answer 4 · answered Apr 18 '13 at 10:21

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

score 1 · Answer 5 · edited Sep 24 '14 at 05:36

1

Try this:

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

edited Sep 24 '14 at 05:36

benomatis

5,536
7
36
59

answered Sep 24 '14 at 04:52

milan

11
1

Sergey Zuyev · Answer 6 · 2021-03-06T13:27:16.760

1

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

edited Mar 06 '21 at 13:27

answered Mar 06 '21 at 13:18

Sergey Zuyev

11
2

score 0 · Answer 7 · edited Oct 21 '14 at 03:04

0

You can try

<column_name> is null

in the where clause.

edited Oct 21 '14 at 03:04

J0e3gan

8,740
10
53
80

answered Oct 21 '14 at 00:17

Fahad Akbar

1

Your answer could be made more to the person asking the question if you add more detail. – Martin Davies Oct 21 '14 at 02:43

score 0 · Answer 8 · edited Mar 08 '16 at 12:58

0

You can try this.....

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

edited Mar 08 '16 at 12:58

Athafoud

2,898
3
40
58

answered Feb 08 '16 at 09:52

Ajay Jamwal

39
7

score 0 · Answer 9 · edited Apr 13 '20 at 12:53

0

declare @sexo as char(1)

select @sexo='F'

select * from pessoa

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

edited Apr 13 '20 at 12:53

Sabri Karagönen

2,212
1
14
28

answered Apr 13 '20 at 10:27

Sérgio Roberto Penha Heredia

1

Null or empty check for a string variable

9 Answers9

Edit:

Linked