Oracle varchar2 equivalent in sql server

Question

create table #temp(name nvarchar(10))
insert into #temp values('one')
select * from #temp where name = 'one'
select * from #temp where name = 'one ' --one with space at end
drop table #temp

In the above I have used nvarchar for name. My requirement is the result should be exist for the first select query, and it should not return for 2nd query. Do not trim the name. Advise me which data type can I use for this in sql server?

Answer 1

Its not the data type that can resolve this issue. You need to see this article: INF: How SQL Server Compares Strings with Trailing Spaces

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.

There are several ways to overcome this, one is to use Like.

select * from #temp where name like 'one ' --one with space at end

This will return no result.

You should see this blog post: Testing strings for equality counting trailing spaces by AnthonyBloesch