SQL WHERE clause matching values with trailing spaces
That's the expected result: in SQL Server the =
operator ignores trailing spaces when making the comparison.
SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.
The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.
Source
Spaces in WHERE clause for SQL Server
Yes, it ignores trailing spaces in comparisons.
You can try to append a delimiting character.
SELECT count(*)
FROM mytable
WHERE col + 'X' = ' X';
Can we stop SQL Server EXCEPT from ignoring trailing spaces in values
According to this article (https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces) :
The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.
This behavior is intended.
You can use a slower method to achieve what you wanted:
SELECT innerItems.ProductName
FROM
(
SELECT DATALENGTH(ProductName) as realLength, ProductName COLLATE SQL_Latin1_General_CP1_CS_AS as ProductName
FROM @SampleLocal
EXCEPT
SELECT DATALENGTH(ProductName) as realLength, ProductName COLLATE SQL_Latin1_General_CP1_CS_AS as ProductName
FROM @RemoteTable
) innerItems
Comparing the values and real lengths together does the magic here. (The len
method would give the 'wrong' result in this case)
Like operator and Trailing spaces in SQL Server
This is a case of reading the documentation, it's very explicitly stated here: http://msdn.microsoft.com/en-us/library/ms179859.aspx
When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.
Edit: According to your comments, you seem to be looking for a way to use like
while ignoring trailing spaces. Use something like this: field like rtrim('abc ')
. It will still use indexes because rtrim()
is a scalar operand and it's evaluated before the lookup phase.
SQL Server : distinct with trailing whitespace
Try this:
SELECT A.myString
FROM (
SELECT DISTINCT myString, DATALENGTH(myString) [DataLength]
FROM #example
) A
With this data:
insert into #example values ('space'),
('space '),
('space2'),
('space2');
Results:
myString
--------
space
space
space2
SQL Server 2008: How to find trailing spaces
You can find trailing spaces with LIKE
:
SELECT col FROM tbl WHERE col LIKE '% '
UNDOCUMENTED FEATURE when SELECT in VARCHAR with trailing whitespace SQL Server
The answer is to add the following clause:
AND DATALENGTH(t.Foo) = DATALENGTH('Bar')
Running the following query...
DECLARE @Chars TABLE (CharNumber INT NOT NULL)
DECLARE @CharNumber INT = 0
WHILE(@CharNumber <= 255)
BEGIN
INSERT INTO @Chars(CharNumber) VALUES(@CharNumber)
SET @CharNumber = @CharNumber + 1
END
SELECT
CharNumber
, IIF('Test' = 'Test' + CHAR(CharNumber),1,0) ['Test' = 'Test' + CHAR(CharNumber)]
, IIF('Test' LIKE 'Test' + CHAR(CharNumber),1,0) ['Test' LIKE 'Test' + CHAR(CharNumber)]
, IIF(LEN('Test') = LEN('Test' + CHAR(CharNumber)),1,0) [LEN('Test') = LEN('Test' + CHAR(CharNumber))]
, IIF(DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber)),1,0) [DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber))]
FROM @Chars
WHERE ('Test' = 'Test' + CHAR(CharNumber))
OR ('Test' LIKE 'Test' + CHAR(CharNumber))
OR (LEN('Test') = LEN('Test' + CHAR(CharNumber)))
ORDER BY CharNumber
...produces the following results...
CharNumber 'Test' = 'Test' + CHAR(CharNumber) 'Test' LIKE 'Test' + CHAR(CharNumber) LEN('Test') = LEN('Test' + CHAR(CharNumber)) DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber))
0 1 1 0 0
32 1 0 1 0
37 0 1 0 0
DATALENGTH can be used to test the equality of two VARCHAR, therefore the original query can be corrected as follows:
-- Create a table variable Note: This same behaviour occurs in standard tables.
DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)
-- Add some test data Note: Without space, space prefix and space suffix
INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')
-- SELECT statement that is filtered by a value without a space and also a value with a space suffix
SELECT
t.Foo
, t.About
FROM @TestResults t
WHERE t.Foo like 'Bar '
AND t.Foo like 'Bar'
AND t.Foo = 'Bar '
AND t.Foo = 'Bar'
AND DATALENGTH(t.Foo) = DATALENGTH('Bar') -- Additional clause
I also made a function to be used instead of =
ALTER FUNCTION dbo.fVEQ( @VarCharA VARCHAR(MAX), @VarCharB VARCHAR(MAX) )
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
-- Added by WonderWorker on 18th March 2020
DECLARE @Result BIT = IIF(
(@VarCharA = @VarCharB AND DATALENGTH(@VarCharA) = DATALENGTH(@VarCharB))
, 1, 0)
RETURN @Result
END
..Here is a test for all 256 characters used as trailing characters to prove that it works..
-- Test fVEQ with all 256 characters
DECLARE @Chars TABLE (CharNumber INT NOT NULL)
DECLARE @CharNumber INT = 0
WHILE(@CharNumber <= 255)
BEGIN
INSERT INTO @Chars(CharNumber) VALUES(@CharNumber)
SET @CharNumber = @CharNumber + 1
END
SELECT
CharNumber
, dbo.fVEQ('Bar','Bar' + CHAR(CharNumber)) [fVEQ Trailing Char Test]
, dbo.fVEQ('Bar','Bar') [fVEQ Same test]
, dbo.fVEQ('Bar',CHAR(CharNumber) + 'Bar') [fVEQ Leading Char Test]
FROM @Chars
WHERE (dbo.fVEQ('Bar','Bar' + CHAR(CharNumber)) = 1)
AND (dbo.fVEQ('Bar','Bar') = 0)
AND (dbo.fVEQ('Bar',CHAR(CharNumber) + 'Bar') = 1)
Related Topics
Lock (Monitor) Internal Implementation in .Net
JSON.Net Serialize Specific Private Field
Http 404 Page Not Found in Web API Hosted in Iis 7.5
Pulling Data from a Webpage, Parsing It for Specific Pieces, and Displaying It
Cancelling a Task Is Throwing an Exception
Can't Convert Value Type Array to Params Object[]
ASP.NET Identity - Multiple Object Sets Per Type Are Not Supported
Serialize a Container of Enums as Strings Using JSON.Net
No Templates in Visual Studio 2017
Get SQL Query from Linq to SQL
Create Dynamic Buttons in a Grid Layout - Create a Magic Square Ui
Programmatically Get a Screenshot of a Page
Optimal Way to Read an Excel File (.Xls/.Xlsx)
How to Merge 2 List<T> and Removing Duplicate Values from It in C#