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