如何在SQL中的一行中声明和分配变量

I want something like

我想要这样

DECLARE myVariable nvarchar[MAX] = "hello world".

Bonus points if you show me how to encode a quote in the string.

如果您向我演示如何在字符串中编码引用，将获得额外的好处。

E.g.:

例如:

I want the string to read

我想让字符串读出来

John said to Emily "Hey there Emily"

my attempt would be

我尝试将

DECLARE myVariable nvarchar[MAX] = "John said to Emily \"Hey there Emily\""

3 个解决方案

#1

113

Here goes:

是:

DECLARE @var nvarchar(max) = 'Man''s best friend';

You will note that the ' is escaped by doubling it to ''.

您将注意到“通过将其加倍来转义”。

Since the string delimiter is ' and not ", there is no need to escape ":

由于字符串分隔符是'而不是'，所以不需要转义。

DECLARE @var nvarchar(max) = '"My Name is Luca" is a great song';

The second example in the MSDN page on DECLARE shows the correct syntax.

在声明的MSDN页面中的第二个示例显示了正确的语法。

#2

on sql 2008 this is valid

在sql 2008中，这是有效的

DECLARE @myVariable nvarchar(Max) = 'John said to Emily "Hey there Emily"'
select @myVariable

on sql server 2005, you need to do this

在sql server 2005上，您需要这样做

DECLARE @myVariable nvarchar(Max) 
select @myVariable = 'John said to Emily "Hey there Emily"'
select @myVariable

#3

You've nearly got it:

你记起来了:

DECLARE @myVariable nvarchar(max) = 'hello world';

See here for the docs

请参阅这里的文档。

For the quotes, SQL Server uses apostrophes, not quotes:

对于引号，SQL服务器使用撇号，而不是引用:

DECLARE @myVariable nvarchar(max) = 'John said to Emily "Hey there Emily"';

Use double apostrophes if you need them in a string:

如果需要在字符串中使用双撇号:

DECLARE @myVariable nvarchar(max) = 'John said to Emily ''Hey there Emily''';

#1

113