Insert the empty string as NULL and zero as zero in the floating column

advertisements

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 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.


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...