SQL Server automatically strips trailing whitespace [duplicate]

Question

Possible Duplicate:
SQL WHERE clause matching values with trailing spaces

When querying SQL Server (through Management Studio Express for what its worth), I've noticed that when querying a varchar field it doesn't matter if you have some trailing whitespace in your criteria, it will still match.

The below matches matched (so it ignores the trailing whitespace in the search criteria)

select top 10 * from sometable
where somecolumn = 'matched    '

I've tried this on 2005 and 2012, same result.

Any ideas why this happens? Is it something to do with how varchar are stored?

Does look to be but the tagging of that question ("C#, Database"!) means it didn't show up in title searches when writing the question. Suggest re-tagging of the old question. If someone that has permissions can do that please. After that then this can be closed as a duplicate. — Mr Grok
– Mr Grok, Commented May 23, 2012 at 8:22

Alexis Stap · Accepted Answer · 2012-05-23 08:02:56Z

4

It is a normal behaviour : http://support.microsoft.com/kb/316626/

'like' doesn't follow that behaviour :

select top 10 * from sometable
where somecolumn like 'matched    '

will not return "matched"

answered May 23, 2012 at 8:02

Alexis Stap

513 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server automatically strips trailing whitespace [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related