Condition WHERE string without/with space-CodePudding

I have table with some strings. I would like make select with condition string = eqauls something
I Dont have any other strings....
The select returns more rows when I Have:
What is wrong?

DECLARE @C VARCHAR(2) = 'A' SPACE(1)
DECLARE @T TABLE (id INT NOT NULL, string VARCHAR(200) NOT NULL)
INSERT INTO @T
(
    id,
    string
)
VALUES
(   1,    'A'), (2,'A' SPACE(1))


SELECT * FROM @T WHERE string = @C--With space only

Returns:

id  string
1   A
2   A

I know hot to make select LIKE '%.... '.
I want to know why TSQL returns more rows.

SQL 2019, MSSQL version 18.9.2

CodePudding user response：

SQL Server follows the ANSI standard when it comes to comparing strings with =. Read a longer description over here: https://dba.stackexchange.com/a/10511/7656

The bottom line is, you can't check for trailing spaces with =. Use LIKE without any % instead.

Given

CREATE TABLE T (id INT NOT NULL, string VARCHAR(200) NOT NULL)
INSERT INTO T VALUES (1, 'A')
INSERT INTO T VALUES (2, 'A ')

this

SELECT id, len(string) len, datalength(string) datalength FROM T

results in

id	len	datalength
1	1	1
2	1	2

and

SELECT id FROM T WHERE string LIKE 'A '

will give you 2. See http://sqlfiddle.com/#!18/2356c9/1

CodePudding user response：

You can use one of the following solutions

-- Option 1: add to the filter the condition `DATALENGTH(@C) = DATALENGTH(string)` or 'DATALENGTH(@C) < DATALENGTH(string)'
SELECT * FROM @T WHERE string = @C and DATALENGTH(@C) <= DATALENGTH(string)

-- Option 2: Use `LIKE` and add the expresion '%'
SELECT * FROM @T WHERE string like @C   '%'