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

Question

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.

Are you inserting other columns as well? Is the money field in your table set to NOT NULL? Because if you have other columns and this one is not set to NOT NULL, then just don't include it on the insert... — JohnHC
– JohnHC, Commented Sep 7, 2016 at 12:56
Re: your last statement, SQL Server's data type conversions are documented on MSDN: msdn.microsoft.com/en-us/library/ms191530.aspx — alroc
– alroc, Commented Sep 7, 2016 at 12:56
@johnhc I am inserting alot more columns and alot of them are of type float, all of them are allowed to be NULL. — Mance
– Mance, Commented Sep 7, 2016 at 13:01
Money as float datatype = bad idea. as for null... INSERT INTO t1 (money) values (NULL) or `INSERT INTO t1 (money) values (case when '' = '' then null else '') — xQbert
– xQbert, Commented Sep 7, 2016 at 13:01
@xQbert the column is in fact AccruedInterest i simplified it for reading purposes as money — Mance
– Mance, Commented Sep 7, 2016 at 13:03

swe · Accepted Answer · 2016-09-07 12:59:36Z

2

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

edited Sep 7, 2016 at 12:59

answered Sep 7, 2016 at 12:55

swe

1,45517 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xQbert Over a year ago

I like the, "you try wierd things if you insert string values into a float-column." :P

Kannan Kandasamy · Accepted Answer · 2016-09-07 13:01:32Z

1

You can try this way

declare @valuetobeinserted varchar(20)
set @valuetobeinserted = '0.00'
insert into t1 select case when  @valuetobeinserted = '' then null else @valuetobeinserted end as m

set @valuetobeinserted = ''
insert into t1 select case when  @valuetobeinserted = '' then null else @valuetobeinserted end as m

answered Sep 7, 2016 at 13:01

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related