Mixing
MS SQL Spaces in WHERE Clause
Clash Royale CLAN TAG#URR8PPP
up vote
7
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 betwenn the amount of spaces? How can I query exactly for one, two or more spaces?
sql
sql-server asked 1 hour ago
Koruba
685
add a comment |Â
up vote
7
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 betwenn the amount of spaces? How can I query exactly for one, two or more spaces?
sql
sql-server asked 1 hour ago
Koruba
685
dba.stackexchange.com/questions/10510/…
– Ivan Starostin
1 hour 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
1 hour ago
If SQL Server would adhere to Standard SQL it would be really simple:where col like ' '
– dnoeth
1 hour ago
add a comment |Â
up vote
7
down vote
favorite
up vote
7
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 betwenn the amount of spaces? How can I query exactly for one, two or more spaces?
sql
sql-server asked 1 hour ago
Koruba
685
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 betwenn the amount of spaces? How can I query exactly for one, two or more spaces?
sql
sql-server sql
sql-server asked 1 hour ago
Koruba
685
asked 1 hour ago
Koruba
685
asked 1 hour ago
Koruba
685
asked 1 hour ago
Koruba
685
asked 1 hour ago
Koruba
685
685
dba.stackexchange.com/questions/10510/…
– Ivan Starostin
1 hour 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
1 hour ago
If SQL Server would adhere to Standard SQL it would be really simple:where col like ' '
– dnoeth
1 hour ago
add a comment |Â
dba.stackexchange.com/questions/10510/…
– Ivan Starostin
1 hour 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
1 hour ago
If SQL Server would adhere to Standard SQL it would be really simple:where col like ' '
– dnoeth
1 hour ago
dba.stackexchange.com/questions/10510/…
– Ivan Starostin
1 hour ago
dba.stackexchange.com/questions/10510/…
– Ivan Starostin
1 hour 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
1 hour 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
1 hour ago
If SQL Server would adhere to Standard SQL it would be really simple:
where col like ' '– dnoeth
1 hour ago
If SQL Server would adhere to Standard SQL it would be really simple:
where col like ' '– dnoeth
1 hour ago
add a comment |Â
4 Answers
4
active
oldest
votes
up vote
3
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 1 hour 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
1 hour ago
1
Unfortunately, this precludes the use of indexes.
– Gordon Linoff
1 hour ago
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 1 hour ago
S.K.
1,814717
add a comment |Â
up vote
0
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 1 hour ago
Mike Bruesch
1516
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:
answered 47 mins ago
Andrea
6,920144248
add a comment |Â
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
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 1 hour 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
1 hour ago
1
Unfortunately, this precludes the use of indexes.
– Gordon Linoff
1 hour ago
add a comment |Â
up vote
3
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 1 hour 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
1 hour ago
1
Unfortunately, this precludes the use of indexes.
– Gordon Linoff
1 hour ago
add a comment |Â
up vote
3
down vote
up vote
3
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 1 hour 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 1 hour ago
sticky bit
10.3k51629
answered 1 hour ago
sticky bit
10.3k51629
answered 1 hour ago
sticky bit
10.3k51629
answered 1 hour ago
sticky bit
10.3k51629
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
1 hour ago
1
Unfortunately, this precludes the use of indexes.
– Gordon Linoff
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
1 hour ago
1
Unfortunately, this precludes the use of indexes.
– Gordon Linoff
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
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
1 hour ago
1
1
Unfortunately, this precludes the use of indexes.
– Gordon Linoff
1 hour ago
Unfortunately, this precludes the use of indexes.
– Gordon Linoff
1 hour ago
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 1 hour ago
S.K.
1,814717
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 1 hour ago
S.K.
1,814717
add a comment |Â
up vote
0
down vote
up vote
0
down vote
You can combine DATALENGTH clause with your query:
select COUNT(*)
from mytable
where col = ' '
and DATALENGTH(col) = 1
answered 1 hour ago
S.K.
1,814717
You can combine DATALENGTH clause with your query:
select COUNT(*)
from mytable
where col = ' '
and DATALENGTH(col) = 1
answered 1 hour ago
S.K.
1,814717
answered 1 hour ago
S.K.
1,814717
answered 1 hour ago
S.K.
1,814717
answered 1 hour ago
S.K.
1,814717
1,814717
add a comment |Â
add a comment |Â
up vote
0
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 1 hour ago
Mike Bruesch
1516
add a comment |Â
up vote
0
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 1 hour ago
Mike Bruesch
1516
add a comment |Â
up vote
0
down vote
up vote
0
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 1 hour ago
Mike Bruesch
1516
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 1 hour ago
Mike Bruesch
1516
answered 1 hour ago
Mike Bruesch
1516
answered 1 hour ago
Mike Bruesch
1516
answered 1 hour ago
Mike Bruesch
1516
1516
add a comment |Â
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:
answered 47 mins ago
Andrea
6,920144248
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:
answered 47 mins ago
Andrea
6,920144248
add a comment |Â
up vote
0
down vote
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:
answered 47 mins ago
Andrea
6,920144248
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:
answered 47 mins ago
Andrea
6,920144248
answered 47 mins ago
Andrea
6,920144248
answered 47 mins ago
Andrea
6,920144248
answered 47 mins ago
Andrea
6,920144248
6,920144248
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52592109%2fms-sql-spaces-in-where-clause%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
dba.stackexchange.com/questions/10510/…
– Ivan Starostin
1 hour 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
1 hour ago
If SQL Server would adhere to Standard SQL it would be really simple:
where col like ' '– dnoeth
1 hour ago