3

I want to have function in sql server similar to ISNULL() except it should check expression for null and empty also.

If first parameter is null or empty, returns second.

can someone help me?

vishal
  • 596
  • 2
  • 12
  • 31

4 Answers4

8

You can use NULLIF and ISNULL:

SELECT ISNULL(NULLIF(@parameter, ''), @parameter2)
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Thanks but I want to write one method, instead of writing case statement everytime. I have big procedure with 100 times ISNULL method used. And I want my method to be compatible with all types like ISNULL – vishal Aug 19 '15 at 07:48
  • @vishal: there is no `CASE` (anymore). I have replaced my case approach with `NULLIF` to treat the empty string as null. – Tim Schmelter Aug 19 '15 at 07:48
  • Yes, That's what I am using after I could not find implementation of ISNULL :) – vishal Aug 19 '15 at 07:50
  • @vishal: You are using `ISNULL` but you didnt know `NULLIF`?? However, you need both or a `CASE` + `COALESCE` approach. – Tim Schmelter Aug 19 '15 at 07:50
  • I know NULLIF also, but I want to have one single method. I already have many solutions but wondering how Microsoft has implemented NULLIF – vishal Aug 19 '15 at 07:53
  • @vishal: probably as simple `CASE WHEN @param = @val Then NULL ELSE @param END` – Tim Schmelter Aug 19 '15 at 08:00
  • Interesting thing about ISNULL is you can pass varchar, int, datetime anything. Select SUM(ISNULL(@number,0)) works but if I wrote my own function, I don't know what parameter type I should return. If I return string, Select SUM(ISNULLOREMPTY(@number,0)) won't work. I hope you got my point what I am looking for :) – vishal Aug 19 '15 at 08:06
  • First you ask how MS has implemented `NULLIF` now you're referring to `ISNULL`. I'm not sure if i understand your requirement. Don't use functions if you can use operators. Functions always prevent index usage hence are very slow. They are a black hole for the optimizer. – Tim Schmelter Aug 19 '15 at 08:11
3

NULLIF will return NULL incase the two parameters passed are same. And ISNULL will return the second value if the first value is NULL.

DECLARE @Input varchar(10) =NULL;
--DECLARE @Input varchar(10) ='';
--DECLARE @Input varchar(10) ='Good';

SELECT ISNULL(NULLIF(@Input,''),'Input is null or blank')
Deep Kalra
  • 1,418
  • 9
  • 27
1

You can use COALESCE:

Eg:

SELECT COALESCE(First,Second,Third,'')
From TableName

COALESCE will return the first non-null value in the list. Read more here

EDIT:

For checking against emptystring, you can use CASE:

SELECT CASE WHEN COALESCE(Field,'')='' THEN 'This is NULL or Empty' END
Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • Thanks for you comment but COALESCE does not check for empty string. – vishal Aug 19 '15 at 07:46
  • how are you checking for blanks? – Deep Kalra Aug 19 '15 at 07:47
  • @RagingBull: Thanks, but I want to have one single method. I already have many solutions but wondering how Microsoft has implemented NULLIF – vishal Aug 19 '15 at 07:55
  • @vishal: the funciton compares the two values and returns NULL if both parameters are equal. Refer to https://msdn.microsoft.com/en-IN/library/ms177562.aspx for more.. – Deep Kalra Aug 19 '15 at 07:57
0

Many years too late but I use this to simplify things...

CREATE FUNCTION dbo.IsNullOrEmpty
(
    @ValueToCheck NVARCHAR(MAX),
    @ReturnValue NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    IF (@ValueToCheck IS NOT NULL AND LTRIM(RTRIM(@ValueToCheck)) != '')
    BEGIN
        SET @ReturnValue = @ValueToCheck
    END

    RETURN  @ReturnValue
END
GO
user13005885
  • 1
  • 1
    Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Nov 10 '22 at 00:30