Collation issue

Hi,

I have a simple issue with collation but not able to find a quick fix or solution. I have a simple name column and I want the search on this column to NOT ignore the blank spaces in the end. For example, SELECT * FROM Table1 WHERE name='all ' should not return me any rows where as SELECT * FROM Table1 WHERE name='all' should return me the rows. I tried setting windows collation and made case sensitive, accent sensitive and width sensitive but it doesnt seem to work. (I dont want to trim the text)

Any suggestions?

Shyam

May be adding symbol at the end could help:

select * from #Table1 where name+'|'='all '+'|'

|||

I would rather trim it but I dont want to touch the sql query now. Any suggestions? in terms of settings/configuration at the sql server level?

Shyam

|||

Hi this is not a collation issue.

It is a specification. You can't achive your desired result with out changing your query. It is a ANSI/ISO SQL-92 specification. As per the specification the len('Abc') = len('Abc ') = len('Abc ') = 3. The trailing space are not counted.

The following query may fix your issue..

SELECT * FROM TABLE WHERE Convert(varbinary,COL1) = convert(varbinary,'')