How to check if field is NULL and blank?

Question

I'm trying to remove the possibility of blank spaces by a value not existing in the database when creating the view for my lookup. The issue I'm having is that my CASE statement isn't working quite right when I'm trying to check for a NULL or blank value. It seems to work for those that are null but the blank doesn't seem to have as much luck. In this case I am trying to check for null or blank of importantField

CREATE VIEW Lookup4 AS
SELECT TOP 140000 CONCAT(no,
    CASE WHEN (importantField is null OR importantField  = '') 
        THEN '' ELSE ' ' + importantField END, 
            fieldname + ' ', anotherField2)  AS UNRELATEDFIELD, Code, 
    CASE NAME
        WHEN '101,,,,,' THEN 'value1e'
        WHEN '14,,,,,' THEN 'value3'
        WHEN '16,,,,,' THEN 'value4'
    END AS NAME
FROM  dbo.Lookup

Please check this question - https://stackoverflow.com/questions/45461543/sql-isnull-is-not-working-and-returns-a-value-even-if-it-is-a-space — Nenad Zivkovic, Sep 01 '17 at 08:49

score 1 · Accepted Answer · answered Sep 01 '17 at 08:56

1

Is this what you are after

CREATE VIEW Lookup4 AS
SELECT TOP 140000 CONCAT(no,
    CASE WHEN (ISNULL(importantField,'') = '') 
        THEN '' ELSE ' ' + importantField END, 
            fieldname + ' ', anotherField2)  AS UNRELATEDFIELD, Code, 
    CASE NAME
        WHEN '101,,,,,' THEN 'value1e'
        WHEN '14,,,,,' THEN 'value3'
        WHEN '16,,,,,' THEN 'value4'
    END AS NAME
FROM  dbo.Lookup

answered Sep 01 '17 at 08:56

Mr Slim

1,458
3
17
28

isnull function requires 2 argument(s). Any ideas? – Alex Sep 01 '17 at 09:02
your tags were SQL and SSMS, which suggested MS SQL TSQL and SQL Server Management Studio. ISNULL in TSQL has 2x arguments and my sample has 2 x arguments ISNULL(fieldToCheck, ReplacementValueIfNull) – Mr Slim Sep 01 '17 at 09:06
It seemed to have worked running it a second time without any changes. Might suggest there's issue with the data in the database, cheers. – Alex Sep 01 '17 at 09:17
1

If there was an issue with the data, wouldn't you have seen the same issue regardless of how many times you executed the query? – Mr Slim Sep 01 '17 at 09:43
Had to add another space after the data. Found that 3k instances had an extra space in them for some reason even though the source file imported didn't have them. It's unusual that 3k / 180k had this happen to them but your query made it easy to sort through. Cheers. – Alex Sep 01 '17 at 09:47

score 0 · Answer 2 · answered Sep 01 '17 at 08:53

0

If you only want to check for null and not for empty strings then you can also use ifnull as you tried. But that is not suitable for empty strings too.

 SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1 
    from tablename

answered Sep 01 '17 at 08:53

hitesh panwar

40
1
10

score 0 · Answer 3 · answered Sep 01 '17 at 08:54

0

Try to change:

importantField is null

with

IsNull(importantField)

answered Sep 01 '17 at 08:54

TeoVr81

989
15
33

score 0 · Answer 4 · answered Sep 01 '17 at 08:56

0

CREATE VIEW Lookup4 AS
SELECT TOP 140000 CONCAT(no,ifnull(importantField,'')<>'', 
            fieldname + ' ', anotherField2)  AS UNRELATEDFIELD, Code, 
    CASE NAME
        WHEN '101,,,,,' THEN 'value1e'
        WHEN '14,,,,,' THEN 'value3'
        WHEN '16,,,,,' THEN 'value4'
    END AS NAME
FROM  dbo.Lookup

answered Sep 01 '17 at 08:56

Chandrakant Thakkar

938
9
23

isnull function requires 2 argument(s). Any ideas? – Alex Sep 01 '17 at 09:02
you can also use isnull same way as ifnull – Chandrakant Thakkar Sep 01 '17 at 09:04

score 0 · Answer 5 · edited Sep 01 '17 at 09:47

0

Minor changes according to your result

Method 1:

 Select *
    From dbo.Lookup
    Where IsNull(importantField, '') = ''

Method 2:

 Select *
    From dbo.Lookup
    Where (importantField is NULL or importantField = '')

edited Sep 01 '17 at 09:47

shishir

851
3
11
27

answered Sep 01 '17 at 09:05

Sandeep Suthar

57
1
12

How to check if field is NULL and blank?

5 Answers5