sql server difference between a blank column and column with value = 'NULL'

Question

In sql server, I see a strange behaviour: When I do

select col1+ ' ' + cast(col2 as varchar(10)) as concat_col

it returns me a column with a value = NULL (not a blank column).

My suspicion is that it is because col2 has a value = NULL (not blank column). So what is the reason for this behavior? But more importantly, what is the meaning of a column with value = NULL as opposed to a blank column? I do not imagine somebody went in the table and updated all the columns with value = NULL.

score 1 · Accepted Answer · answered Aug 29 '12 at 23:32

1

NULL means that the field does not have any value. You can use ISNULL function to convert a null value into something that you want.The following will hopefully give you the correct result (will not give null)

select ISNULL(col1,'')+ ' ' + cast(ISNULL(col2,'') as varchar(10)) as concat_col

answered Aug 29 '12 at 23:32

TheTechGuy

16,560
16
115
136

SO this is different from oracle i guess. when i open an oracle table in toad, both null and '' show as blank column – Victor Aug 30 '12 at 02:22
In Oracle you have to use `NVL` and you can use `coalesce` which is supported in both. More in [this stackoverflow question](http://stackoverflow.com/a/3524035/777982) – TheTechGuy Aug 30 '12 at 13:11

hjarrell · Answer 2 · 2012-08-29T23:42:38.330

0

Blank is a value while null is completely nothing.

Think of zero is a number while null is nothing.

edited Aug 29 '12 at 23:42

answered Aug 29 '12 at 23:34

hjarrell

31
1
7

sql server difference between a blank column and column with value = 'NULL'

2 Answers2