問題描述
我一直在查看這段代碼復(fù)制如下，它尋找非 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?
推薦答案
這是正常的.創(chuàng)建變量時，它采用數(shù)據(jù)庫的默認排序規(guī)則.
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 的字符串被隱式轉(zhuǎn)換為帶有數(shù)據(jù)庫默認排序規(guī)則的字符串.
Your string with COLLATE Latin1_General_BIN is implicitly casted to string with your database default collation.
<小時>例如數(shù)據(jù)庫是Case-Insensitive.我使用您的語法創(chuàng)建區(qū)分大小寫的語法并檢查它的元數(shù)據(jù):
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 ║
╚══════╩══════════════════════════════╝
變量(不包括表變量中的列)不允許定義排序規(guī)則，因此沒有如下語法:
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'.
這篇關(guān)于整理聲明的 SQL 變量的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！