使用 Null 连接 SQLServer 字符串

我正在创建一个跨字段的计算列，其中一些字段可能为空。

问题是，如果这些字段中的任何一个为空，则整个计算列将为空。我从 Microsoft 文档中了解到，这是预期的，可以通过设置 SETCONCAT _ NULL _ YIELDS _ NULL 来关闭。但是，在那里我不想更改这个默认行为，因为我不知道它对 SQLServer 的其他部分的影响。

有没有一种方法可以让我检查一个列是否为 null，并且只在它不为 null 的情况下将其内容附加到计算列公式中？

174354 次浏览
小开

Use COALESCE. Instead of your_column use COALESCE(your_column, ''). This will return the empty string instead of NULL.

小开

ISNULL(ColumnName, '')

小开
最佳答案

You can use ISNULL(....)

SET @Concatenated = ISNULL(@Column1, '') + ISNULL(@Column2, '')

If the value of the column/expression is indeed NULL, then the second value specified (here: empty string) will be used instead.

小开

In Sql Server:

insert into Table_Name(PersonName,PersonEmail) values(NULL,'xyz@xyz.com')


PersonName is varchar(50), NULL is not a string, because we are not passing with in single codes, so it treat as NULL.

Code Behind:

string name = (txtName.Text=="")? NULL : "'"+ txtName.Text +"'";
string email = txtEmail.Text;


insert into Table_Name(PersonName,PersonEmail) values(name,'"+email+"')
小开

I just wanted to contribute this should someone be looking for help with adding separators between the strings, depending on whether a field is NULL or not.

So in the example of creating a one line address from separate fields

Address1, Address2, Address3, City, PostCode

in my case, I have the following Calculated Column which seems to be working as I want it:

case
when [Address1] IS NOT NULL
then (((          [Address1]      +
isnull(', '+[Address2],'')) +
isnull(', '+[Address3],'')) +
isnull(', '+[City]    ,'')) +
isnull(', '+[PostCode],'')
end

Hope that helps someone!

小开

From SQL Server 2012 this is all much easier with the CONCAT function.

It treats NULL as empty string

DECLARE @Column1 VARCHAR(50) = 'Foo',
@Column2 VARCHAR(50) = NULL,
@Column3 VARCHAR(50) = 'Bar';




SELECT CONCAT(@Column1,@Column2,@Column3); /*Returns FooBar*/
小开

You can also use CASE - my code below checks for both null values and empty strings, and adds a seperator only if there is a value to follow:

SELECT OrganisationName,
'Address' =
CASE WHEN Addr1 IS NULL OR Addr1 = '' THEN '' ELSE Addr1 END +
CASE WHEN Addr2 IS NULL OR Addr2 = '' THEN '' ELSE ', ' + Addr2 END +
CASE WHEN Addr3 IS NULL OR Addr3 = '' THEN '' ELSE ', ' + Addr3 END +
CASE WHEN County IS NULL OR County = '' THEN '' ELSE ', ' + County END
FROM Organisations
小开

Use 

SET CONCAT_NULL_YIELDS_NULL  OFF

and concatenation of null values to a string will not result in null.

Please note that this is a deprecated option, avoid using. See the documentation for more details.

小开

I had a lot of trouble with this too. Couldn't get it working using the case examples above, but this does the job for me:

Replace(rtrim(ltrim(ISNULL(Flat_no, '') +
' ' + ISNULL(House_no, '') +
' ' + ISNULL(Street, '') +
' ' + ISNULL(Town, '') +
' ' + ISNULL(City, ''))),'  ',' ')

Replace corrects the double spaces caused by concatenating single spaces with nothing between them. r/ltrim gets rid of any spaces at the ends.

小开

This example will help you to handle various types while creating insert statements

select
'insert into doc(Id, CDate, Str, Code, Price, Tag )' +
'values(' +
'''' + convert(nvarchar(50), Id) + ''',' -- uniqueidentifier
+ '''' + LEFT(CONVERT(VARCHAR, CDate, 120), 10) + ''',' -- date
+ '''' + Str+ ''',' -- string
+ '''' + convert(nvarchar(50), Code)  + ''',' -- int
+ convert(nvarchar(50), Price) + ',' -- decimal
+ '''' + ISNULL(Tag, '''''') + '''' + ')'  -- nullable string


from doc
where CDate> '2019-01-01 00:00:00.000'

提交答案