[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