Comparing '' against ' ' - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Comparing '' against ' '

Author

Topic

mateamargo
Starting Member

10 Posts

Posted - 2006-03-27 : 14:21:35

I have a field on a table that is char(1), and I want to bring every record that has a space on that field.

But, if I run this query:

select * from table where filed = ''

Brings me the fields that have an space ' ' too. Is there any way to make a comparison from '' and ' '.

Thanks. (Sorry my bad english).

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-27 : 15:14:49

What do you want to find ?
Field is Null ?
Field Contains, zero length string ('') ?
Field Contains, space in the data('abc pqr') ?
Field Contains, quotation characters ?

What is char(1) field ?

mateamargo
Starting Member

10 Posts

Posted - 2006-03-27 : 15:38:33

create table [_example] (field char(1))
insert _example select ' '
insert _example select 'x'
select * from _ejemplo where field = ''

In this case I don't any result, but SQL Server asumes that '' is equal to ' '
But I need to differentiate because the application insert sometimes a space ' ', and the default value is ''.

Tahsin
Starting Member

34 Posts

Posted - 2006-03-27 : 15:54:47

Should read: SELECT * FROM ~~_ejemplo~~ _example
Switching to spanish tense won't return in the select

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-27 : 16:22:59

mateamargo,
Just a hint
Read the article for sp_dbcmptlevel - T-SQL --> in BOL

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-03-27 : 16:59:01

Char(1) field is never empty, because it is fixed length string. When '' is passed to variable it is stored as ' '.

mateamargo
Starting Member

10 Posts

Posted - 2006-03-28 : 08:36:14

Yes mmarovic, but suppose you do this:

if '' <> ' ' print 'Differents' else print 'Equals'

SQL Server assumes that both strings are empty. I would like to know if is there any way to differentiate them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 08:40:59

One of the tricky ways is to append a character and compare


declare @t table (field char(1))
insert @t select ' '
insert @t select 'x'
select * from @t where field = ''         -- wrong result
select * from @t where field+'a' = ''+'a' -- Correct result

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-03-28 : 08:43:49

I still don't get why do you want to test against empty string in char(1) column when you know in advance it is not possible. All you need is to test against ' ' (one space) and null if the column is nullable.

The behaviour you describe is probably a bug, but it should not affect you.

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-28 : 09:06:23

Test like this:

'.' + '' + '.' <> '.' + ' ' + '.'

This is something commonly used in batch scripts

if %1* == %VAR%* do ....

* serves the same purpose as dots above, but in sql u should append on both sides.

BTW, I am really surprised by the fact that len('') equals len(' ')

www.r-moth.com

mateamargo
Starting Member

10 Posts

Posted - 2006-03-28 : 09:23:49

Thanks majkinetor by the hint!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 09:30:02

Mirko, can you highlight the change you made?

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-03-28 : 09:35:31

Just fixed a typo, I think behavior described is a bug not a bag.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-28 : 11:37:59

quote:
Originally posted by majkinetor

Test like this:

'.' + '' + '.' <> '.' + ' ' + '.'

This is something commonly used in batch scripts

if %1* == %VAR%* do ....

* serves the same purpose as dots above, but in sql u should append on both sides.

BTW, I am really surprised by the fact that len('') equals len(' ')

www.r-moth.com

You would not be surprised if you read the documentation.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_3e7i.asp

quote:

LEN

Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
Syntax

LEN ( string_expression )
Arguments

string_expression

Is the string expression to be evaluated.
Return Types

int

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-28 : 17:12:32

How did I miss that ....

www.r-moth.com http://r-moth.deviantart.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources