Why do I get NULL when adding/concatenating a NULL value with SQL 7? I didn't with 6.5.

A. Look up the SET concat_null_yields_null ON/OFF command/option in the Books Online. To meet ANSI standards the default for this is on.

When it is on SELECT 'abc' + NULL will be NULL. With it off you will get 'abc'

N.B. Even if you set this off at the database level be warned that it could be turned on again at the application level either through the ODBC DSN setting or the application configuration - e.g. Query Analyser turns this on by default and you have to turn it off via the file/configure menu otherwise it will override your database setting!

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.