整理声明的SQL变量

我一直在看这个代码,下面转载,寻找非ASCII字符......

select line,
  patindex('%[^ !-~]%' COLLATE Latin1_General_BIN, Line) as [Position],
  substring(Line, patindex('%[^ !-~]%' COLLATE Latin1_General_BIN, Line), 1) as [InvalidCharacter],
  ascii(substring(line, patindex('%[^ !-~]%' COLLATE Latin1_General_BIN, Line), 1)) as [ASCIICode]
from staging.APARMRE1
where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN, Line) > 0

我只想告诉我,我想要声明一个变量,'%[^ !-~]%' COLLATE Latin1_General_BIN而不是每次都写出来,但是

declare @regex varchar(20) = '%[^ !-~]%' COLLATE Latin1_General_BIN;

select line,
  patindex(@regex, Line) as [Position],
  substring(Line, patindex(@regex, Line), 1) as [InvalidCharacter],
  ascii(substring(line, patindex(@regex, Line), 1)) as [ASCIICode]
from staging.APARMRE1
where patindex(@regex, Line) > 0

只是不做同样的事情.我只是缺少一些语法？这不可能吗？

这是正常的.创建变量时,它会对数据库采用默认排序规则.

DECLARE @regex varchar(20) = '%[^ !-~]%' COLLATE Latin1_General_BIN;

你的字符串COLLATE Latin1_General_BIN被隐式铸造串与数据库默认排序规则.

DECLARE @v1 varchar(100) = 'ABC' COLLATE Latin1_General_CS_AS;

SELECT name, collation_name
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT @v1 AS [@v1]', N'@v1 varchar(100)', 0);

LiveDemo

输出:

???????????????????????????????????????
? name ?        collation_name        ?
???????????????????????????????????????
? @v1  ? SQL_Latin1_General_CP1_CI_AS ?
???????????????????????????????????????

变量(表变量中的列除外)不允许定义排序规则,因此没有类似的语法:

DECLARE @v1 varchar(100) COLLATE Latin1_General_CS_AS = 'ABC' ;
-- Incorrect syntax near the keyword 'COLLATE'.