2

SQL Server 6.5 appears to not differentiate between zero length strings and strings containing a single space.

It is true in all versions of SQL Server that Trailing spaces are ignored in = comparisons but this issue is different.

This SELECT returns 1:

SELECT DATALENGTH('')

I have a table with a varchar(20) column FAX upon which this update seems to have no effect:

UPDATE CLIENT
SET FAX = ''  -- The existing field consists only of a single space
WHERE ClientNo = 124

The field still contains a blank space even after this update is run.

Trimming a field that consists only of spaces results in a NULL value, which is undesirable for our purposes.

Is it possible to replace blank values with an empty string? How would I go about it?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
ilitirit
  • 16,016
  • 18
  • 72
  • 111
  • 2
    SQL-Server 6.5? As in the one from 1996? – Bridge Jan 30 '13 at 16:06
  • Yes, that's the one. We're in the process of migrating data out of a legacy system. – ilitirit Jan 30 '13 at 16:07
  • I can't even find the documentation any more - good luck! – Bridge Jan 30 '13 at 16:10
  • 3
    Good to hear you're moving away from it... 6.5 is no longer supported, to the point that patches are no longer produced for security issues. This should have been done before March 31 of 2004, when that support ended. Hopefully moving forward, you'll point management to the [product lifecycle guide](http://support.microsoft.com/lifecycle/search/?sort=PN&alpha=SQL) and make sure management is aware that software has an expiration date. You need to check this at both the operating system and sql server levels. And while you're viewing that link, note that Sql Server 2000 expires in <3 months. – Joel Coehoorn Jan 30 '13 at 17:17

1 Answers1

-1

Does SET ANSI_NULLS ON make any difference?

eidgenossen
  • 129
  • 5