single space and empty string
-
Hi
My database ANSI null and set ansi_null_dflt_on setting are both on. With these being said.
Does the following two select statement return same result?
Select * from table1 where col_1 <>'' and col_1<> ' ' and col_1 is not null
Select * from table1 where col_1 <>'' and col_1 is not null
Thanks
-
No
The Empty String and SPACE are both valid value and there should not be compare as NULL....
cheers
-
I guess I should reword my question. I know that '' and ' ' are both valid values and I am not comparing them with NULL. I am trying to compare '' and ' ' . Are they the same thing? In other words , If I run the following two select statement will I have some amount of records returned.
select * from table1 where col1=''
select * from table1 where col1= '' and col1 = ' '
Thanks
-
If there are some record exists with the SPACE value then surely the answer will be No.
cheers
-
It seems from my testing that both the '' and ' ' are treated the same. This doesn't seem right since I'd expect a '' to be stored as ...0000000 and the ' ' to be stored a ...0100000, but it's not looking that way.
create table mytable
( id int identity(1,1)
, mychar varchar(20)
)
go
insert mytable select null
insert mytable select ''
insert mytable select ' '
go
set ansi_nulls on
set ansi_null_dflt_on on
select * from mytable
where mychar is not null
select * from mytable
where mychar '' and mychar is not null
select * from mytable
where mychar ' ' and mychar is not null
select * from mytable where mychar = ''
select * from mytable where mychar = ' '
set ansi_null_dflt_on off
set ansi_nulls off
go
drop table mytable
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
hmm...and here's another test where they're treated the same too...maybe michelle should use datalength..
create table #Spaces(col1 varchar(5), col2 varchar(5))
go
insert into #Spaces values(' ', '')
insert into #Spaces values('', ' ')
insert into #Spaces values('', '')
select * from #Spaces where col1= ' '
select *, datalength(col1) lenCol1, datalength(col2) lenCol2 from #Spaces where col1=''
drop table #Spaces
**ASCII stupid question, get a stupid ANSI !!!** -
Select 'The Same'
Where 'test' = 'test '
-
Select 'Not the same'
Where datalength('test') <> datalength('test ')
Select 'The Same'
Where len('test') = len('test ')
**ASCII stupid question, get a stupid ANSI !!!** -
SQL Server does sometimes treat a space as the same as an empty string.
This occurs when the database compatibility is set to 65 or lower.
To quote MSDN:
Whether SQL Server interprets an empty string as either a single space or as a true empty string is controlled by the compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information about setting the compatibility level, see sp_dbcmptlevel (Transact-SQL).
SQL guy and Houston Magician
-
So I set the compatibility level to 60, then 65, 70 and 80....when I ran the following script against each setting I got the exact same results....
sp_dbcmptlevel @dbname = 'MyDatabase', @new_cmptlevel = 60/65/70/80
if len('test') = len('test ')
print 'the same'
else
print 'not the same'
if datalength('test') = datalength('test ')
print 'the same'
else
print 'not the same'
**ASCII stupid question, get a stupid ANSI !!!** -
From BOL....
"...setting the compatibility level of a database to 65 makes the database version-6.5 compatible, but does not necessarily provide version 6.5 behaviors. For example, when SET ANSI_PADDING is ON and you attempt to insert the strings 'abc' and 'abc ' into a primary key column, SQL Server 2000 considers the strings to be duplicates and does not violate the primary key constraint. In SQL Server 6.5, the two strings are considered to be unique and both insertions succeed. Setting the compatibility level to 65 does not force SQL Server 2000 to treat the strings as unique values."
**ASCII stupid question, get a stupid ANSI !!!** -
Last post I promise....
I set the compatibility level to 60 - "messed around" some and then forgot to reset it back to 80...
I went in just now to look at some tables and I started getting all sorts of error messages...including one where I couldn't even look at the design of a table via EM....it's a good thing I went into the database when I did for if I had done so tomorrow or later, this post would have been faaaar from my mind and I'd have been floundering for some considerable time...
**ASCII stupid question, get a stupid ANSI !!!** -
Your example will give you the same results nomatter how your db is configured.
Len will always implicitly trim trailing spaces, datalength will not. Be aware, though, that datalength and len actually tell you different things. Datalength gives you the length, in bytes of your string, and len tells you the length in charactors. If you are using NVARCHAR types (which uses double byte strings) you may get unexpected results.
SQL guy and Houston Magician
-
Sorry - My example really should've been:
if 'test' = 'test '
print 'the same'
else
print 'not the same'
**ASCII stupid question, get a stupid ANSI !!!** -
Hi all,
I did a couple test as steve suggested. it turned out no matter mychar is varchar or char, the '' and ' ' are treated as same. I guess I got my answer.
Thanks a lot!!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply