SQL Standard for NULL Concatenation

Use set sqlnull on to implement SQL standard for NULL concatenation.

Standard SQL requires that string concatenation involving a NULL generates a NULL output. SAP ASE evaluates a string concatenated with NULL to the value of the string. A string concatenation involving a NULL is treated as a string with a 0 length and an empty string ("") is interpreted as a single space.  

SAP ASE allows you to use the set sqlnull option to implement SQL standard for NULL concatenation.

This example, based on the table staff_profile, demonstrates the different output generated using the sqlnull option:

create table staff_profile(id int, firstname char(10) NULL, 
surname char(10) NULL, city varchar(10) NULL, country varchar(10) NULL ) 
go 
insert staff_profile values(001, 'Tom', 'Griffin', 'Dublin', 'US') 
insert staff_profile values(002, 'Kumar', NULL, 'Pune', 'India') 
insert staff_profile values(003,  NULL , 'Kobe', 'Tokyo', NULL) 
insert staff_profile values(004, 'Steve', 'Lewis', 'London', 'UK') 
insert staff_profile values(005, 'Hana', 'SAP', NULL, 'Germany') 
insert staff_profile values(006, 'Wei', 'Ming', 'Shanghai', 'China')
insert staff_profile values(007, 'city-state', '  ', 'Singapore', '')
Output with the default value of set sqlnull off:
set sqlnull off
select id, rtrim(firstname) + '' + rtrim(surname) name, rtrim(city) + '' + rtrim(country) 
location from staff_profile

id           name           location         
-------- ------------ ---------------------             
1 Tom Griffin             Dublin US                         
2 Kumar                   Pune India                        
3 Kobe                    Tokyo                             
4 Steve Lewis             London UK                         
5 Hana SAP                Germany                          
6 Wei Ming                Shanghai China
7 city-state              Singapore                      
(6 rows affected)  

Output with set sqlnull on:

set sqlnull on 
select id, rtrim(firstname) + '' + rtrim(surname) name, rtrim(city) + '' + rtrim(country) 
location from staff_profile
    
id           name           location         
-------- ------------ ---------------------            
1 Tom Griffin             Dublin US                         
2 NULL                    Pune India                        
3 NULL                    NULL                              
4 Steve Lewis             London UK                         
5 Hana SAP                NULL                              
6 Wei Ming                Shanghai China 
7 city-state              Singapore       
(6 rows affected)