October 5, 2017 at 1:56 am
Hi All,
Question is related to sql server behavior. Suppose, if I am inserting, blanks into a column, It is storing the value as 0.
Why? I am using sql server 2016. Is this expected behaviour? The reason, I am asking this because we have some ETL process which has views which selects data
from staging tables and load into dimensions. The staging table columns can have blank spaces and if the dimension has the column datatype defined as INT or numeric datatype, then
it might fail with datatype conversion error. So, we have decided to put a CASE statement and replace '' with NULLs.
While doing that, i came across this below behaviour that blanks are treated/getting inserted as 0. WHY??
Other thing is that, if there is genuine 0 value in column id, then it will be difficult to differentiate betweem blanks and zero.
How to handle it? Can anyone shed some light on this?
SQL Server details:
Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64) Jul 6 2017 07:55:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)
create table test
(id varchar(10)
)
insert into test
select ''
union all
select ' '
union all
select 1
union all
select 2
union all
select ' '
union all
select 3
SELECT ID,LEN(ID) AS ID_LENGTH FROM TEST
ID ID_LENGTH
0 1
0 1
1 1
2 1
0 1
3 1
Thanks in advance,
Sam
October 5, 2017 at 2:19 am
Hi Sam,
The select statement which you are inserting with is implicitly converting the blank values to integers.
If you run the select below the insert on its own, it returns the zeros
If you qualify the integer values with single quotes ' , it will insert the values as Varchar.
I have tested on SQL 2016 and SQL 2014, the behavior is the same for both.
Try this
create table test
(id varchar(10)
)
insert into test
select ''
union all
select ' '
union all
select '1'
union all
select '2'
union all
select ' '
union all
select '3'
Hope this helps
Curtis
October 5, 2017 at 2:19 am
Sam
You hint at the answer in the title of your thread. Since you're inserting a column of values in one go, those values all have to have the same data type, so they are converted to the type with the highest precedence, which is int. As you have seen, a blank and a white space are converted to 0. You'll get different results on the other hand, if you change your INSERT logic slightly:
create table #test3
(id varchar(10)
);
insert into #test3 select '';
insert into #test3 select ' ';
insert into #test3 select 1;
insert into #test3 select 2;
insert into #test3 select ' ';
insert into #test3 select 3;
SELECT ID,LEN(ID) AS ID_LENGTH FROM #test3;
John
October 5, 2017 at 2:24 am
The reason for this is that due to data type precedents. In your UNION statement all the values are in the same column and you are providing integers and varchars. In a column all the values must have the same datatype, so they are converted to the the data type with the higher precedence (or the query fails); integers have a higher precedence than varchars. Thus the '' values are converted to integers which = 0.
If you're inserting items into a varchar field, provide them as varchars. I.e.insert into test
select ''
union all
select ' '
union all
select '1'
union all
select '2'
union all
select ' '
union all
select '3';
You'll notice there, that the blank strings remain as blank strings, because every value is a varchar; I haven't mixed and matched my data types.
Otherwise, instead of inserting a blank string (which are then converted to a integers) insert a NULL and have your column data type set to the type it should be (int).CREATE TABLE test
(id int);
INSERT INTO test
SELECT NULL
UNION ALL
SELECT NULL
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT NULL
UNION ALL
SELECT 3;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2017 at 4:14 am
Got it. Thanks all for the explainations.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply