Insert empty string as NULL and zero as zero into float colum

Question

I have a float column, call it money.
I am trying to insert the 0 value as 0 and the empty string value should be

NULL

INSERT INTO t1 (money) values ('0.00')

INSERT INTO t1 (money) values ('')



SELECT * FROM t1

This is the result I am trying to achieve

 money

    0

    NULL

I tried Using an

INSTEAD OF INSERT

trigger.(I used '123' just to visualize which conidition is met )

iif(CAST(money AS nvarchar) = '', NULL, iif(money < '0.0001', '123', money))



SELECT * FROM t1



money

123

123

So it seems that I cannot compare my float column against an empty string.

But when trying following it looks like in fact it is possible:

CASE WHEN (money = '' THEN '456' ELSE iif(money < '0.0001', '123', money))



SELECT * FROM t1



money 

456

456

Which makes me very unsure of how sqlserver converts those datatypes internally.

Answer 1

Uhm,

you try wierd things if you insert string values into a float-column.

INSERT INTO t1 (money) values (nullif('0.00', ''))

BUT you should really invest some time in data type conversion from ground up. I would expect the calling application to have an error if someone tried to insert a string into my float-column...

Insert empty string as NULL and zero as zero into float colum

Sign up