Empty string should be NULL when converting to float

Question

Consider the following TSQL code:

declare @a nvarchar(500) = N''
select try_convert(float, @a)

The output is:

I need the output to be NULL.

I can do this:

declare @a nvarchar(500) = N''
select case @a
           when '' then null
           else try_convert(float, @a)
       end

and it works just fine.

However, this is just a mock-up. In my real life scenario, instead of @a, there are over 200 NVARCHAR(500) columns, either floats or zero length strings. I need a quick way of converting zero-length strings to NULL (and everything else to float), possibly without having to build 200 separate CASE statements.

For a zero-length string there is an implicit conversion from varchar to int. — Abhishek
– Abhishek, Commented Jun 12, 2018 at 7:39

Gordon Linoff · Accepted Answer · 2018-06-12 11:38:55Z

4

I'm not really thrilled with relying the rather inexplicable differences between try_parse() and try_convert()/try_cast(). Instead, I would go for:

try_convert(float, nullif(@a, ''))

This also has the advantage of being quite explicit in what you are trying to accomplish.

answered Jun 12, 2018 at 11:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

DhruvJoshi · Accepted Answer · 2018-06-12 07:58:09Z

3

You should use Try_parse instead

declare @a nvarchar(500) = N''
select try_parse( @a as float)

returns

NULL

See working demo

answered Jun 12, 2018 at 7:58

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

Collectives™ on Stack Overflow

Empty string should be NULL when converting to float

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related