[freetds] String comparisons not working correctly, FreeTDS + PHP + MSSQL 2005

James K. Lowden jklowden at freetds.org
Wed Apr 30 14:35:13 EDT 2008


Reid, Roger L. wrote:
> The problem isn't char vs varchar, but the nvarchar.   It's a Unicode
> string in the database.   It needs to convert SOMETHING to do the
> comparison, since 'Fred' is not a Unicode string.   Easy check (and fix)
> - prepend N to 'Fred' thus:
> 
> 	where UweName = N'Fred';
> 
> What your query is doing is correct for a fixed width char field - if
> UweName were char(15), you'd need to do
> 	where rtrim(UweName) = 'Fred';
> but that could be a lot of work for the server in some circumstances.

If that's true, why does this work?  


1> select cast( 'Fred' as nvarchar(15)) as name into #a 
2> select * from #a where name = 'Fred   '
3> go
(1 row affected)
 name
 ------------------------------
 Fred

1> select * from #a where name = 'Fred'
2> go
 name
 ------------------------------
 Fred

AFAIK trailing spaces are insignificant for any character column
regardless of encoding or server/database/connection options.  

A Microsoft server does a lot of implicit encoding conversions.  Remember
the same thing happens when you query the INFORMATION_SCHEMA or say

	exec sp_help 'tablename'

because the parameter (and sysobjects.name) are both of type nvarchar.  

--jkl



More information about the FreeTDS mailing list