Is There a Opposite Function to Isnull in SQL Server? to Do Is Not Null

Is there a opposite function to ISNULL in sql server? To do Is not null?

You have to use CASE

SELECT CASE WHEN Field IS NOT NULL
    THEN 'something'
    ELSE 'something else'
END

IS NOT NULL and ISNULL( str, NULL ) in WHERE clause

We found this to be an issue in Sql Server 2008 with no service packs installed. Try installing SP1 or SP2. In our case, the service pack resolved the issue.

SQL Server : ISNULL(compound NULL condition, 'a string') returns only the 1st character, under certain circumstance(s)

There are two parts to this problem, the first is the nature of the ISNULL operator, it will use the datatype and length of the first argument. A simple example would be:

DECLARE @A CHAR(1) = NULL,
        @B VARCHAR(MAX) =  'This is a test';

SELECT TOP 1 Test = ISNULL(@A, @B);

This returns T and checking the execution plan XML we can see the implicit conversion of "This is a Test" to CHAR(1):

<ScalarOperator ScalarString="isnull([@A],CONVERT_IMPLICIT(char(1),[@B],0))">
    <Intrinsic FunctionName="isnull">
    <ScalarOperator>
        <Identifier>
        <ColumnReference Column="@A" />
        </Identifier>
    </ScalarOperator>
    <ScalarOperator>
        <Convert DataType="char" Length="1" Style="0" Implicit="true">
        <ScalarOperator>
            <Identifier>
            <ColumnReference Column="@B" />
            </Identifier>
        </ScalarOperator>
        </Convert>
    </ScalarOperator>
    </Intrinsic>
</ScalarOperator>

Your example is not quite so straightforward since you don't have your types nicely defined like above, but if we do define the dataypes:

DECLARE @A VARCHAR(MAX) =  'a big old string',
        @B VARCHAR(MAX) = 'needle',
        @C VARCHAR(MAX) = 'haystack';

SELECT TOP 1 ISNULL(LEFT(@A, NULLIF(CHARINDEX(@B, @C), 0) - 1), @A);

We get the result as expected. So something else is happening under the hood. The query plan does not delve into the inner workings of the constant evaluation, but the following demonstrates what is happening:

SELECT  Test = LEFT('a big old string', NULLIF(CHARINDEX('needle', 'haystack'), 0) - 1)
INTO    #T;

SELECT  t.name, c.max_length
FROM    tempdb.sys.columns AS c
        INNER JOIN sys.types AS t
            ON t.system_type_id = c.system_type_id
            AND t.user_type_id = c.user_type_id
WHERE   [object_id] = OBJECT_ID(N'tempdb..#T');

----------------
name        max_length
varchar     1

Basically, by using the SELECT INTO sytax with your left expression shows that a when the NULL length is passed to LEFT the resulting datatype is VARCHAR(1), however, this is not always the case. If I simply hard code NULL into the LEFT function:

SELECT  Test = LEFT('a big old string', NULL)
INTO    #T;

--------------------
name        max_length
varchar     16

Then you get the length of the string passed, but a case expression that should be optimised away to the same thing, yields a length of 1 again:

SELECT  TOP 1 Test = LEFT('a big old string', CASE WHEN 1 = 1 THEN NULL ELSE 1 END)
INTO    #T;

----------------
name        max_length
varchar     1

I suspect it is related to the default behaviour of VARCHAR, where the default length is 1, e.g:

DECLARE @A VARCHAR = 'This is a Test';

SELECT  Value = @A,                                         -- T
        MaxLength = SQL_VARIANT_PROPERTY(@A, 'MaxLength')   -- 1

But I can't tell you why you would see different behaviour for NULL and CASE WHEN 1 = 1 THEN NULL ELSE 1 END. If you wanted to get the bottom of what is going on in the constant evaluation I think you would probably need to re-ask on the DBA site and hope that one of the real SQL Server Gurus picks it up.

In summary, LEFT(<constant>, <constant expression>) where <constant expression> yields NULL is implicitly typed as VARCHAR(1), and this implicit type is used in ISNULL evaluation.

For what it is worth, if you explicitly type the result of your LEFT function then you get the expected result:

SELECT ISNULL(
            CAST(
                LEFT(
                    'a big old string', 
                    NULLIF(CHARINDEX('needle', 'haystack'), 0) - 1
                    ) 
                AS VARCHAR(MAX))
                , 'a big old string');

---

An additional point is that when you say you don't want to repeat any expressions (If 0 < CHARINDEX, then take CHARINDEX - 1, etc.), there are two things you should know, the first is that NULLIF(<expression>, <value>) expands to a case expression - CASE WHEN <expression> = <value> THEN NULL ELSE <expression> END, so is repeated, the second is that this doesn't matter, SQL Server can identify that this is the same expression used twice, and will evaluate it once and refer to the same result each time it is used.

using sql - Is not null in a select statement

You can use CASE:

SELECT a. ....
       (CASE WHEN a.Error1 IS NOT NULL 
             THEN ' - ' + a.Error1 + CHAR(13)+CHAR(10)  
             ELSE ''
        END) +
       (CASE WHEN a.Error2 IS NOT NULL 
             THEN ' - ' + a.Error2 + CHAR(13)+CHAR(10)  
             ELSE ''
        END) +
       (CASE WHEN a.Error3 IS NOT NULL 
             THEN ' - ' + a.Error3 + CHAR(13)+CHAR(10)  
             ELSE ''
        END) +

   ...etc 

IsNull() sql function

Remove Number from second parameter of ISNULL function.

Anything + NULL = NULL

so make the ISNULL to result 0 when it is NULL and then add 1 to the result

Update SomeTable set Number = IsNull(Number, 0) + 1 where
ItemCode = '000000' ;

or

Update SomeTable set Number = IsNull(Number+1, 1) where
ItemCode = '000000' ;

or two different updates (not recommended)

Update SomeTable set Number = Number + 1 where
ItemCode = '000000' AND Number IS NOT NULL;

Update SomeTable set Number = 1 where
ItemCode = '000000' AND Number IS NULL;

What is the PostgreSQL equivalent for ISNULL()

SELECT CASE WHEN field IS NULL THEN 'Empty' ELSE field END AS field_alias

Or more idiomatic:

SELECT coalesce(field, 'Empty') AS field_alias

Is there a concept which is the 'opposite' of SQL NULL?

No, there's no "universal acceptor" value in SQL that is equal to everything.

What you can do is raise the NVL into your comparison. Like if you're trying to do a JOIN:

SELECT ...
FROM my_table AS m
JOIN other_table AS o ON o.name = NVL(m.name, o.name)

So if m.name is NULL, then the join will compare o.name to o.name, which is of course always true.

For other uses of NULL, you might have to use another technique that suits the situation.

Inverse of COALESCE

Here is a moderately unpleasant way of doing it:

set ansi_nulls off
if (null in (a, b, c, d, e) print 'got a null'
set ansi_nulls on

---

Related Topics