In ASE, the NULL string behaves like an empty string, regardless of any setting. In MS SQL Server, a NULL string behaves like any NULL value, unless the setting CONCAT_NULL_YIELDS_NULL is set to OFF.

Example

set CONCAT_NULL_YIELDS_NULL OFF

select 'A' + NULL + 'B' -- Result is AB

set CONCAT_NULL_YIELDS_NULL ON

select 'A' + NULL + 'B' -- Result is NULL