Scalar-valued function does not return NULL but a 'NULL' string

Question

I need to import data from Excel into a ms sql database and I thought using the OPENROWSET would be a good idea... well, it is not bad but has some side effects.

The data I'm receiving is not allways 100% correct. By correct I mean cells that should be NULL (and in Excel empty) sometimes contain the string "NULL" or some other junk like whitespaces. I tried to fix it with this script:

GO    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[NullIfEmpty](@input nvarchar)
RETURNS nvarchar(max)
AS
BEGIN

    if (@input = '' or @input = 'NULL')
    begin
        return NULL
    end

    return @input

END

But strange things happen. This gives me a string with the text "NULL" instead of a real NULL so the grid cell after querying the database isn't yellow but contains normal text even though the target column allows NULL.

A simple test with:

select dbo.nullifempty('NULL')

or

select dbo.nullifempty(null)

also yields a string.

Do you know why this is happening and how I can fix it?

Zohar Peled · Accepted Answer · 2018-11-05 09:58:11Z

2

To get null for empty strings or strings that are the word NULL, you could just use coalesce and nullif:

COALESCE(NULLIF(@input, 'NULL'), NULLIF(@Input, ''), @input)

Please note that the problem in your original code is because you didn't specify the length of the @input parameter - so SQL Server created it as varchar(1).
You should always specify length for char/varchar/nchar and nvarchar.

From nchar and nvarchar page remarks:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

(n referring to the n in nchar(n) or nvarchar(n))

edited Nov 5, 2018 at 9:58

answered Nov 5, 2018 at 9:52

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

t3chb0t Over a year ago

that's why there were single 'N's in some columns. I didn't know where they came from.

pi.314 · Accepted Answer · 2018-11-05 09:39:39Z

2

repleace lines with 'ALTER"

ALTER FUNCTION [dbo].[NullIfEmpty](@input nvarchar(max))

and with line with 'if'

if (LTRIM(RTRIM(@input)) = '' or @input IS NULL)

edited Nov 5, 2018 at 9:39

answered Nov 5, 2018 at 9:31

pi.314

6205 silver badges17 bronze badges

4 Comments

t3chb0t Over a year ago

This is a nice improvement but still producing a string ;-(

pi.314 Over a year ago

do you want to return NULL when function called with string 'null' as well ?

t3chb0t Over a year ago

@Vasily and P. ejH - by your powers combined the output is now correct! ;-)

Zohar Peled Over a year ago

In the original function, if @input is null it should fail the condition and return @input - which is null...

Vasily · Accepted Answer · 2018-11-05 09:44:02Z

1

you should reassign value for declared variable using set

''''
BEGIN

    if (@input = '' or @input = 'NULL')
    begin
        set @input = NULL
    end

    select @input

END

test

edited Nov 5, 2018 at 9:44

answered Nov 5, 2018 at 9:33

Vasily

5,7923 gold badges22 silver badges36 bronze badges

1 Comment

t3chb0t Over a year ago

ok, now I'm starting to believe my sql server is stupid. Even if I run the example script alone in a new query I still see a string... what happened to my sql server? :-o this can't be :-\

Collectives™ on Stack Overflow

Scalar-valued function does not return NULL but a 'NULL' string

3 Answers 3

1 Comment

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related