SQL Server 2005 LENfunction oddity

Question

The following code:

if (right(@eqlist,2) = ', ')
  set @eqlist = left(@eqlist,len(@eqlist)-2)

produces different results (with the same data) on my production server than on my development server.

The problem is that the LEN function is 'supposed' to trim off the trailing spaces before calculating the length of a string, but it doesn't seem to be doing so on my development server.

On my development server, it works fine and strips off the trailing ', ' from a string, but on the production server, it's stripping off an extra character. Obviously, changing the "-2" to a "-1" fixes the problem for the production server, but then breaks it for the development server.

I rewrote the code so that it works consistently on both servers, but I'd still like to know why the LEN function behaves differently on each server.

The rewritten code:

if (right(@eqlist,2) = ', ')
  set @eqlist = left(rtrim(@eqlist),len(rtrim(@eqlist))-1)

Answer 1

Check the character sets and the collation settings on your two servers, databases and tables. Differences may result in one being trimmed and the other not.

Answer 2

Have you checked the value of ANSI_PADDING when your table(s) were created? Without full specifics on your issue I can't say for sure, but if you're going against a table with VARCHAR columns this could cause it to act differently.