Spaces in WHERE clause for SQL Server

up vote
8
down vote

favorite

I want to find out the records in which a certain column contains exactly one space and nothing else. So I wrote the first of the following queries:

select COUNT(*)
from mytable
where col = ' ' -- One space

select COUNT(*)
from mytable
where col = ' ' -- Two spaces

select COUNT(*)
from mytable
where col = ' ' -- Three spaces

However, all three queries return the exact same records. Does Microsoft SQL Server not distinguish between the amount of spaces? How can I query exactly for one, two or more spaces?

edited 11 mins ago

Peter Mortensen

13k1983111

asked 4 hours ago

Koruba

735

dba.stackexchange.com/questions/10510/â€¦
â€“Â Ivan Starostin
4 hours ago

Its simple: looks into empty string. doesnt matter if its one or two spaces. try it this way 'value ' and check result set
â€“Â Hadrian
4 hours ago

If SQL Server would adhere to Standard SQL it would be really simple:where col like ' '
â€“Â dnoeth
3 hours ago

add a commentÂ |Â

up vote
8
down vote

favorite

I want to find out the records in which a certain column contains exactly one space and nothing else. So I wrote the first of the following queries:

select COUNT(*)
from mytable
where col = ' ' -- One space

select COUNT(*)
from mytable
where col = ' ' -- Two spaces

select COUNT(*)
from mytable
where col = ' ' -- Three spaces

However, all three queries return the exact same records. Does Microsoft SQL Server not distinguish between the amount of spaces? How can I query exactly for one, two or more spaces?

edited 11 mins ago

Peter Mortensen

13k1983111

asked 4 hours ago

Koruba

735

dba.stackexchange.com/questions/10510/â€¦
â€“Â Ivan Starostin
4 hours ago

Its simple: looks into empty string. doesnt matter if its one or two spaces. try it this way 'value ' and check result set
â€“Â Hadrian
4 hours ago

If SQL Server would adhere to Standard SQL it would be really simple:where col like ' '
â€“Â dnoeth
3 hours ago

add a commentÂ |Â

up vote
8
down vote

favorite

I want to find out the records in which a certain column contains exactly one space and nothing else. So I wrote the first of the following queries:

select COUNT(*)
from mytable
where col = ' ' -- One space

select COUNT(*)
from mytable
where col = ' ' -- Two spaces

select COUNT(*)
from mytable
where col = ' ' -- Three spaces

However, all three queries return the exact same records. Does Microsoft SQL Server not distinguish between the amount of spaces? How can I query exactly for one, two or more spaces?

edited 11 mins ago

Peter Mortensen

13k1983111

asked 4 hours ago

Koruba

735

I want to find out the records in which a certain column contains exactly one space and nothing else. So I wrote the first of the following queries:

select COUNT(*)
from mytable
where col = ' ' -- One space

select COUNT(*)
from mytable
where col = ' ' -- Two spaces

select COUNT(*)
from mytable
where col = ' ' -- Three spaces

However, all three queries return the exact same records. Does Microsoft SQL Server not distinguish between the amount of spaces? How can I query exactly for one, two or more spaces?

sql sql-server

edited 11 mins ago

Peter Mortensen

13k1983111

asked 4 hours ago

Koruba

735

edited 11 mins ago

Peter Mortensen

13k1983111

asked 4 hours ago

Koruba

735

edited 11 mins ago

Peter Mortensen

13k1983111

edited 11 mins ago

Peter Mortensen

13k1983111

edited 11 mins ago

Peter Mortensen

13k1983111

asked 4 hours ago

Koruba

735

asked 4 hours ago

Koruba

735

asked 4 hours ago

Koruba

735

dba.stackexchange.com/questions/10510/â€¦
â€“Â Ivan Starostin
4 hours ago

Its simple: looks into empty string. doesnt matter if its one or two spaces. try it this way 'value ' and check result set
â€“Â Hadrian
4 hours ago

If SQL Server would adhere to Standard SQL it would be really simple:where col like ' '
â€“Â dnoeth
3 hours ago

add a commentÂ |Â

dba.stackexchange.com/questions/10510/â€¦
â€“Â Ivan Starostin
4 hours ago

Its simple: looks into empty string. doesnt matter if its one or two spaces. try it this way 'value ' and check result set
â€“Â Hadrian
4 hours ago

If SQL Server would adhere to Standard SQL it would be really simple:where col like ' '
â€“Â dnoeth
3 hours ago

dba.stackexchange.com/questions/10510/â€¦
â€“Â Ivan Starostin
4 hours ago

Its simple: looks into empty string. doesnt matter if its one or two spaces. try it this way 'value ' and check result set
â€“Â Hadrian
4 hours ago

If SQL Server would adhere to Standard SQL it would be really simple:where col like ' '
â€“Â dnoeth
3 hours ago

add a commentÂ |Â

4 Answers
4

active

oldest

votes

up vote
6
down vote

Yes, it ignores trailing spaces in comparisons.

You can try to append a delimiting character.

SELECT count(*)
FROM mytable
WHERE col + 'X' = ' X';

answered 4 hours ago

sticky bit

10.3k51629

Technically the standard requires the shorter string be padded with spaces until it has the same length as the longer string. In practice though, I've never found a situation where that would produce a different outcome than trimming/ignoring the trailing spaces.
â€“Â Damien_The_Unbeliever
4 hours ago

2

Unfortunately, this precludes the use of indexes.
â€“Â Gordon Linoff
3 hours ago

1

@GordonLinoff Could that be solved with WHERE col = ' ' AND col+'X' = ' X'? The first part is indexed, then it applies the refinement.
â€“Â Barmar
1 hour ago

add a commentÂ |Â

up vote
2
down vote

The link posted by Ivan Starostin in the comments of the OP provides a good explanation and I think it deserves a full answer instead of just a comment.

To summarize, try using LIKE instead of equality:

select COUNT(*)
from mytable
where col LIKE ' ' -- one space

And you can also use DATALENGTH to calculate how many bytes are in the field to double-check field length:

select col, DATALENGTH(col)
from mytable;

Please note that DATALENGTH will return a different value if col is a VARCHAR vs NVARCHAR. VARCHAR stores each character as 1 byte where NVARCHAR stores each character as 2 bytes since NVARCHAR is stored in Unicode.

answered 3 hours ago

Mike Bruesch

3517

add a commentÂ |Â

up vote
0
down vote

You can combine DATALENGTH clause with your query:

 select COUNT(*)
 from mytable
 where col = ' '
 and DATALENGTH(col) = 1

answered 4 hours ago

S.K.

1,824717

add a commentÂ |Â

up vote
0
down vote

You can replace the single space with a single character (for exampe Ã‚Â§) and then put this character in your where condition:

declare @tmp table(col varchar(50))

insert into @tmp values
(' '),
(' '),
(' ')

select COUNT(*) as one_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§'

select COUNT(*) as two_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§'

select COUNT(*) as three_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§Ã‚Â§'

Results:

enter image description here

answered 3 hours ago

Andrea

6,922144248

add a commentÂ |Â

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52592109%2fspaces-in-where-clause-for-sql-server%23new-answer', 'question_page');

);

Post as a guest

Name

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

up vote
6
down vote

Yes, it ignores trailing spaces in comparisons.

You can try to append a delimiting character.

SELECT count(*)
FROM mytable
WHERE col + 'X' = ' X';

answered 4 hours ago

sticky bit

10.3k51629

Technically the standard requires the shorter string be padded with spaces until it has the same length as the longer string. In practice though, I've never found a situation where that would produce a different outcome than trimming/ignoring the trailing spaces.
â€“Â Damien_The_Unbeliever
4 hours ago

2

Unfortunately, this precludes the use of indexes.
â€“Â Gordon Linoff
3 hours ago

1

@GordonLinoff Could that be solved with WHERE col = ' ' AND col+'X' = ' X'? The first part is indexed, then it applies the refinement.
â€“Â Barmar
1 hour ago

add a commentÂ |Â

up vote
6
down vote

Yes, it ignores trailing spaces in comparisons.

You can try to append a delimiting character.

SELECT count(*)
FROM mytable
WHERE col + 'X' = ' X';

answered 4 hours ago

sticky bit

10.3k51629

Technically the standard requires the shorter string be padded with spaces until it has the same length as the longer string. In practice though, I've never found a situation where that would produce a different outcome than trimming/ignoring the trailing spaces.
â€“Â Damien_The_Unbeliever
4 hours ago

2

Unfortunately, this precludes the use of indexes.
â€“Â Gordon Linoff
3 hours ago

1

@GordonLinoff Could that be solved with WHERE col = ' ' AND col+'X' = ' X'? The first part is indexed, then it applies the refinement.
â€“Â Barmar
1 hour ago

add a commentÂ |Â

up vote
6
down vote

Yes, it ignores trailing spaces in comparisons.

You can try to append a delimiting character.

SELECT count(*)
FROM mytable
WHERE col + 'X' = ' X';

answered 4 hours ago

sticky bit

10.3k51629

Yes, it ignores trailing spaces in comparisons.

You can try to append a delimiting character.

SELECT count(*)
FROM mytable
WHERE col + 'X' = ' X';

answered 4 hours ago

sticky bit

10.3k51629

answered 4 hours ago

sticky bit

10.3k51629

answered 4 hours ago

sticky bit

10.3k51629

answered 4 hours ago

sticky bit

10.3k51629

Technically the standard requires the shorter string be padded with spaces until it has the same length as the longer string. In practice though, I've never found a situation where that would produce a different outcome than trimming/ignoring the trailing spaces.
â€“Â Damien_The_Unbeliever
4 hours ago

2

Unfortunately, this precludes the use of indexes.
â€“Â Gordon Linoff
3 hours ago

1

@GordonLinoff Could that be solved with WHERE col = ' ' AND col+'X' = ' X'? The first part is indexed, then it applies the refinement.
â€“Â Barmar
1 hour ago

add a commentÂ |Â

Technically the standard requires the shorter string be padded with spaces until it has the same length as the longer string. In practice though, I've never found a situation where that would produce a different outcome than trimming/ignoring the trailing spaces.
â€“Â Damien_The_Unbeliever
4 hours ago

2

Unfortunately, this precludes the use of indexes.
â€“Â Gordon Linoff
3 hours ago

1

@GordonLinoff Could that be solved with WHERE col = ' ' AND col+'X' = ' X'? The first part is indexed, then it applies the refinement.
â€“Â Barmar
1 hour ago

Technically the standard requires the shorter string be padded with spaces until it has the same length as the longer string. In practice though, I've never found a situation where that would produce a different outcome than trimming/ignoring the trailing spaces.
â€“Â Damien_The_Unbeliever
4 hours ago

Unfortunately, this precludes the use of indexes.
â€“Â Gordon Linoff
3 hours ago

@GordonLinoff Could that be solved with WHERE col = ' ' AND col+'X' = ' X'? The first part is indexed, then it applies the refinement.
â€“Â Barmar
1 hour ago

add a commentÂ |Â

up vote
2
down vote

The link posted by Ivan Starostin in the comments of the OP provides a good explanation and I think it deserves a full answer instead of just a comment.

To summarize, try using LIKE instead of equality:

select COUNT(*)
from mytable
where col LIKE ' ' -- one space

And you can also use DATALENGTH to calculate how many bytes are in the field to double-check field length:

select col, DATALENGTH(col)
from mytable;

answered 3 hours ago

Mike Bruesch

3517

add a commentÂ |Â

up vote
2
down vote

The link posted by Ivan Starostin in the comments of the OP provides a good explanation and I think it deserves a full answer instead of just a comment.

To summarize, try using LIKE instead of equality:

select COUNT(*)
from mytable
where col LIKE ' ' -- one space

And you can also use DATALENGTH to calculate how many bytes are in the field to double-check field length:

select col, DATALENGTH(col)
from mytable;

answered 3 hours ago

Mike Bruesch

3517

add a commentÂ |Â

up vote
2
down vote

The link posted by Ivan Starostin in the comments of the OP provides a good explanation and I think it deserves a full answer instead of just a comment.

To summarize, try using LIKE instead of equality:

select COUNT(*)
from mytable
where col LIKE ' ' -- one space

And you can also use DATALENGTH to calculate how many bytes are in the field to double-check field length:

select col, DATALENGTH(col)
from mytable;

answered 3 hours ago

Mike Bruesch

3517

The link posted by Ivan Starostin in the comments of the OP provides a good explanation and I think it deserves a full answer instead of just a comment.

To summarize, try using LIKE instead of equality:

select COUNT(*)
from mytable
where col LIKE ' ' -- one space

And you can also use DATALENGTH to calculate how many bytes are in the field to double-check field length:

select col, DATALENGTH(col)
from mytable;

answered 3 hours ago

Mike Bruesch

3517

answered 3 hours ago

Mike Bruesch

3517

answered 3 hours ago

Mike Bruesch

3517

answered 3 hours ago

Mike Bruesch

3517

add a commentÂ |Â

up vote
0
down vote

You can combine DATALENGTH clause with your query:

 select COUNT(*)
 from mytable
 where col = ' '
 and DATALENGTH(col) = 1

answered 4 hours ago

S.K.

1,824717

add a commentÂ |Â

up vote
0
down vote

You can combine DATALENGTH clause with your query:

 select COUNT(*)
 from mytable
 where col = ' '
 and DATALENGTH(col) = 1

answered 4 hours ago

S.K.

1,824717

add a commentÂ |Â

up vote
0
down vote

You can combine DATALENGTH clause with your query:

 select COUNT(*)
 from mytable
 where col = ' '
 and DATALENGTH(col) = 1

answered 4 hours ago

S.K.

1,824717

You can combine DATALENGTH clause with your query:

 select COUNT(*)
 from mytable
 where col = ' '
 and DATALENGTH(col) = 1

answered 4 hours ago

S.K.

1,824717

answered 4 hours ago

S.K.

1,824717

answered 4 hours ago

S.K.

1,824717

answered 4 hours ago

S.K.

1,824717

add a commentÂ |Â

up vote
0
down vote

You can replace the single space with a single character (for exampe Ã‚Â§) and then put this character in your where condition:

declare @tmp table(col varchar(50))

insert into @tmp values
(' '),
(' '),
(' ')

select COUNT(*) as one_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§'

select COUNT(*) as two_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§'

select COUNT(*) as three_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§Ã‚Â§'

Results:

enter image description here

answered 3 hours ago

Andrea

6,922144248

add a commentÂ |Â

up vote
0
down vote

You can replace the single space with a single character (for exampe Ã‚Â§) and then put this character in your where condition:

declare @tmp table(col varchar(50))

insert into @tmp values
(' '),
(' '),
(' ')

select COUNT(*) as one_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§'

select COUNT(*) as two_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§'

select COUNT(*) as three_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§Ã‚Â§'

Results:

enter image description here

answered 3 hours ago

Andrea

6,922144248

add a commentÂ |Â

up vote
0
down vote

You can replace the single space with a single character (for exampe Ã‚Â§) and then put this character in your where condition:

declare @tmp table(col varchar(50))

insert into @tmp values
(' '),
(' '),
(' ')

select COUNT(*) as one_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§'

select COUNT(*) as two_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§'

select COUNT(*) as three_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§Ã‚Â§'

Results:

enter image description here

answered 3 hours ago

Andrea

6,922144248

You can replace the single space with a single character (for exampe Ã‚Â§) and then put this character in your where condition:

declare @tmp table(col varchar(50))

insert into @tmp values
(' '),
(' '),
(' ')

select COUNT(*) as one_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§'

select COUNT(*) as two_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§'

select COUNT(*) as three_space_count
from @tmp
where replace(col,' ','Ã‚Â§')='Ã‚Â§Ã‚Â§Ã‚Â§'

Results:

enter image description here

answered 3 hours ago

Andrea

6,922144248

answered 3 hours ago

Andrea

6,922144248

answered 3 hours ago

Andrea

6,922144248

answered 3 hours ago

Andrea

6,922144248

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky