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

Question

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

What are you using to insert the data from an Excel file to your Table? — Randolph
– Randolph, Commented Aug 11, 2017 at 2:48
I use Bulk Copy to a temporary table and then insert the data to destination table. My query used by my developer in VB.Net and get error while compiling. but its already resolved. thanks for your concern. — Dody Suhada
– Dody Suhada, Commented Aug 11, 2017 at 6:19

Dale K · Accepted Answer · 2024-06-17 22:17:36Z

3

Based off your comments you just need a case expression to your insert from Excel, which I assume is using open query or bulk insert.

Insert into #temp_table (id, model, Plandate, plan_qty, Remark)
Select
...
Case when someColumn = '' then null else someColumn end
...
From
-- your Excel file via openquery or whatever ...

edited Jun 17, 2024 at 22:17

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Aug 11, 2017 at 2:49

S3S

25.2k5 gold badges29 silver badges48 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2017-08-11 02:04:39Z

1

You can't insert an empty string into a numeric field. You can insert NULL:

insert into #temp_table (id, model, Plandate, plan_qty, Remark)
    values ('112325', '10TPB220MC', '2017-04-01', NULL, 'Fresh And Manual');

Of course, the default default value is NULL, so you can also just leave it out:

insert into #temp_table (id, model, Plandate, Remark)
    values ('112325', '10TPB220MC', '2017-04-01', 'Fresh And Manual');

answered Aug 11, 2017 at 2:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Dody Suhada Over a year ago

thank you, I have try this one and this is OK. but my data is from excel file and there is blank cell and my query read it as empty string.

Justinas Jakavonis · Accepted Answer · 2022-09-22 18:28:07Z

0

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

edited Sep 22, 2022 at 18:28

answered Sep 22, 2022 at 18:15

Justinas Jakavonis

8,95812 gold badges82 silver badges118 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related