整理声明的 SQL 变量

时间:2022-11-04
本文介绍了整理声明的 SQL 变量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我一直在查看这段代码复制如下,它寻找非 ASCII 字符......

I've been looking at this code, reproduced below, that looks for non-ASCII characters...

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 声明一个变量,而不是每次都写出来,但是

and it just strikes me that I'd want to declare a variable for '%[^ !-~]%' COLLATE Latin1_General_BIN instead of writing it out every time, but

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

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

just doesn't do the same thing. Am I just missing some syntax? Is it impossible?

推荐答案

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

It is normal. When you create a variable it takes default collation for database.

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

带有 COLLATE Latin1_General_BIN 的字符串被隐式转换为带有数据库默认排序规则的字符串.

Your string with COLLATE Latin1_General_BIN is implicitly casted to string with your database default collation.

<小时>例如数据库是Case-Insensitive.我使用您的语法创建区分大小写的语法并检查它的元数据:


For example database is Case-Insensitive. I use your syntax to create case-sensitive one and check metadata of it:

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 ║
╚══════╩══════════════════════════════╝

变量(不包括表变量中的列)不允许定义排序规则,因此没有如下语法:

Variables(excluding columns in table variables) do not allow to define collation so there is no syntax like:

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

这篇关于整理声明的 SQL 变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!