Assign -1 if column has null value or blank value

Question

I have a case where if my column has NULL value or blank value I have to return '-1'. I have NULL check as follows

ISNULL(IEF.Col1,'-1').

How can i check for blank value and null value(as above) at the same time. Means, if the column has blank value still in select -1 should be returned and if NULL also -1 should be returned.

Answer 1

You can treat empty as null with NULLIF:

ISNULL(NULLIF(IEF.LeadingValue, ''), '-1')

Answer 2

use case inside isnull -

ISNULL( case when IEF.LeadingValue ='' then null else IEF.LeadingValue end ,'-1')

Answer 3

The following code should work:

ISNULL(NULLIF(RTRIM(LTRIM(IEF.LeadingValue)),''),'-1')

Explanation:

• NULLIF - compares two strings , if identical then return null
• RTRIM - right trim , remove right spaces in string
• LTRIM - left trim , remove left spaces in string

Answer 4

You can use NULLIF like:

select ISNULL(NULLIF(IEF.Col1, ''), -1) AS Col1 from table

From Microsoft Website:

Returns a null value if the two specified expressions are equal. For example, SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different; returns NULL for the first column (4 and 4) because the two input values are the same. The second column returns the first value (5) because the two input values are different.

Answer 5

A little trick is: - this will check value just one time -

select
    case when IEF.Col1 <> '' then IEF.Col1
    else '-1'
    end
from
    IEF;

[ SQL Fiddle Demo]

Note: Comparing a null value by using <> operator always returns false.