SQL Server char and nchar columns search different

Question

We have a strange problem. We are upgrading JDE and the database schema is changing - some char columns are changing to nchar types. However, we have found that some of the searches are no longer working, and we have found this to be consistent across our SQL Server 2008 databases:

In the DB that I tested, the ItemNumber column is a char(25) and has varying length contents.

SELECT * FROM TableName WHERE ItemNumber LIKE '%S'

returns a bunch of rows. However, if we change the column to an nchar(25) that query now only returns those rows that have an ItemNumber value ending in "S" and are 25 characters long, so it seems that trailing spaces are now being (possibly correctly) taken into account - if you change the wildcard value to '%S ' it finds 24 character item numbers ending in "S".

Obviously, this is quite an issue for us as *S searches no longer work in JDE, as the underlying database calls now need every nchar column to be trimmed. Is this a known issue, or a setting somewhere that we need to change?

Additional Information We don't have any control over the column types used, nor can we change the underlying SQL generated, as this is part of our ERP system and its upgrade. We have logged a call with Oracle, but as far as I am aware they haven't seen this, nor can they replicate it (but we don't know under what circumstances they are trying to do this), plus the fact that it happens across our other databases/servers makes me wonder if it isn't an obscure setting, somewhere.

Answer 1

EDIT: My explanation below was based of a misreading of your question. While RTRIM will work, I did not realize you weren't using nvarchar but char. Aaron and Gordon have provided better insight.

SELECT * FROM TableName WHERE RTRIM(ItemNumber) LIKE N'%S'  

This is because for an nvarchar(25), the last character of 'S' is S.

For nchar(25), 'S' is actually 'S' + 24 spaces. So your last character is a space.

Answer 2

From the documentation for LIKE (Transact-SQL):

When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant.

I reproduced your problem with the following table:

DECLARE @t TABLE(x NCHAR(25));
INSERT @t SELECT N'nanaS';
SELECT x FROM @t WHERE x LIKE N'%S';

Result:

(0 row(s) affected)

However if you use NVARCHAR instead, this problem does not occur:

DECLARE @t TABLE(x NVARCHAR(25));
INSERT @t SELECT N'nanaS';
SELECT x FROM @t WHERE x LIKE N'%S';

Results:

x
-----
nanaS

However the original table didn't produce the desired results even if converting to NVARCHAR in the WHERE clause:

DECLARE @t TABLE(x NCHAR(25));
INSERT @t SELECT N'nanaS';
SELECT x FROM @t WHERE CONVERT(NVARCHAR(25),x) LIKE N'%S';

Result:

(0 row(s) affected)

So one potential workaround would be to use the right data type in the first place (and also always prefix Unicode strings N'properly'. If you can't make the data type correct, you can use the RTRIM() workaround posted by Aushin, but keep HLGEM's comments in mind as well.