How to insert empty string (' ') to decimal or numeric in SQL Server?

user_6807281 0
Viewed 18590

Is there a way to insert an empty string (' ') into a column of type decimal?

This is for the example :

create table #temp_table 
(
    id varchar(8) NULL,
    model varchar(20) NULL,
    Plandate date NULL,
    plan_qty decimal (18, 0) NULL,
    Remark varchar (50) NULL,
)

insert into #temp_table (id, model, Plandate, plan_qty, Remark)
values ('pn-01', 'model-01', '2017-04-01', '', 'Fresh And Manual')

I get an error

Error converting data type varchar to numeric.

My data is from an Excel file and has some blank cells.

My query read that as an empty string.

If there is a way, please help.

Thanks

numeric decimal sql-server sql string
3 Answers

As it was mentioned, could insert NULL instead of empty string so one more option is to use NULLIF. In your case:

insert into #temp_table (id, model, Plandate, plan_qty, Remark)
values ('pn-01', 'model-01', '2017-04-01', NULLIF(value_which_may_be_empty, ''), 'Fresh And Manual')

Related: Insert empty string into INT column for SQL Server

user_59627660
Related