Mance Mance - 2 months ago 11
SQL Question

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

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.

swe swe
Answer

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

Comments