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)