CODEWITHSUNDEEP
sqlsql-serverssms
Alex
Alex

Reputation: 683

How to check if field is NULL and blank?

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

Upvotes: 2

Views: 1798

Answers (5)

Sandeep Suthar
Sandeep Suthar

Reputation: 57

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 = '')

Upvotes: 0

Chandrakant Thakkar
Chandrakant Thakkar

Reputation: 978

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

Upvotes: 0

Mr Slim
Mr Slim

Reputation: 1458

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

Upvotes: 1

TeoVr81
TeoVr81

Reputation: 1009

Try to change:

importantField is null

with

IsNull(importantField)

Upvotes: 0

hitesh panwar
hitesh panwar

Reputation: 40

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

Upvotes: 0

Related Questions