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
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 condition 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 SQL Server converts those datatypes internally.
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...