sql - Insert empty string as NULL and zero as zero into float column -

i have float column, call money.

i trying insert 0 value 0 , empty string value should null

insert t1 (money) values ('0.00') insert t1 (money) values ('')  select * t1

this result trying achieve

 money     0     null

i tried using instead of insert trigger (i used '123' visualize condition met)

iif(cast(money nvarchar) = '', null, iif(money < '0.0001', '123', money))  select * t1  money 123 123

so seems cannot compare float column against empty string.

but when trying following looks in fact possible:

case when (money = '' '456' else iif(money < '0.0001', '123', money))  select * t1  money  456 456

which makes me unsure of how sql server converts datatypes internally.

uhm,

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

insert t1 (money) values (nullif('0.00', ''))

but should invest time in data type conversion ground up. expect calling application have error if tried insert string float-column...

Today