Mance Mance - 2 months ago 10
SQL Question

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

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.

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