Menu
I use + to concatenate several columns's value. But + doesnt work if one of that columns has null value. For example
Select null+ 'Test'
query returns null instead of 'Test'.
What are your advices to solve that problem?
500
Concatenating Data When There Are NULL Values To resolve the NULL values in string concatenation, we can use the ISNULL() function. In the below query, the ISNULL() function checks an individual column and if it is NULL, it replaces it with a space. We can verify the output of the data as shown below.
To concatenate null to a string, use the + operator. Let's say the following is our string. String str = "Demo Text"; We will now see how to effortlessly concatenate null to string.
The result of a join of null with any other value is null. Because null values represent unknown or inapplicable values, Transact-SQL has no basis to match one unknown value to another. You can detect the presence of null values in a column from one of the tables being joined only by using an outer join.
On versions prior to SQL Server 2012 you should use
Select ISNULL(YourColumn,'') + 'Test' /*Or COALESCE(YourColumn,'')*/
to avoid this issue.
There is a connection option SET CONCAT_NULL_YIELDS_NULL OFF but that is deprecated.
SQL Server 2012 introduces the CONCAT function that treats NULL as an empty string when concatenating.
SELECT CONCAT(null,'Test')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
