使用特殊字符检查字符串是否有效

Question

我使用PATINDEX来验证列是否具有无效的特殊字符。但我在一些字符方面遇到了一些问题。

SELECT PATINDEX(N'%[^a-zA-Z0-9 !"&''()*+,-./:;?=%~@[]_{}|<>]%' collate SQL_Latin1_General_CP850_BIN, 'abc╢123' collate SQL_Latin1_General_CP850_BIN)

有效字符为! " & ' ( ) * + , - . / : ; ? = % ~ @ [ ] _ { } | < >。

╢不是有效的字符之一，但PATINDEX仍然返回0。

有没有其他方法可以验证这一点？

Answer 1

问题似乎是由模式中的]引起的。您可以将LIKE运算符与ESCAPE一起使用。最小示例：

-- valid characters are digits, "[" and "]"
SELECT val
, expected
, CASE WHEN val LIKE '%[^0-9[]]%'             THEN 'Bad' ELSE 'Good' END AS wrong_pattern
, CASE WHEN val LIKE '%[^0-9[]]%' ESCAPE '' THEN 'Bad' ELSE 'Good' END AS right_pattern
FROM (VALUES
('12345', 'Good'),
('[123]', 'Good'),
('ABCDE', 'Bad'),
('1235', 'Bad')
) AS t(val, expected)

所以你的模式可以写成：

SELECT CASE WHEN 'abc╢123!"&''()*+,-./:;?=%~@[]_{}|<>' NOT LIKE '%[^a-z0-9 []\!"&''()*+,-./:;?=%~@_{}|<>]%' ESCAPE '' THEN 'Good' ELSE 'Bad' END

我逃离了]、-和。字符%和_在方括号内不起通配符的作用，因此没有转义。

Answer 2

转义是一种方法
但是，如果您只想忽略可读的ASCII字符，则可以简化范围。

[^ -~]：不在空间和~之间

-- Sample data
declare @T table (col NVARCHAR(30) collate SQL_Latin1_General_CP850_BIN primary key);
insert into @T (col) values
(N'abc╢123'),
(N'xyz123[}'''),
(N'abc௹123');
-- Query
SELECT col, PATINDEX(N'%[^ -~]%' collate  SQL_Latin1_General_CP850_BIN, col) as pos
FROM @T;

退货：

col         pos
--------    ----
abc╢123     4
abc௹123     4
xyz123[}'   0

但要同时定位插入符号和其他一些符号，就更复杂了
因为PATINDEX不像LIKE那样有ESCAPE。

-- Sample data
declare @T table (
id int identity(1,1) primary key, 
col NVARCHAR(30) collate SQL_Latin1_General_CP850_BIN
);
insert into @T (col) values
(N'xyz[123]}''') -- good
,(N'abc╢123') -- bad
,(N'abc௹123') -- bad
,(N'def#456') -- bad
,(N'def^456') -- bad
;
-- also locate #, ´ , ` and ^
SELECT col, 
CASE 
WHEN PATINDEX(N'%[^ !"$-_a-z{-~]%' collate  SQL_Latin1_General_CP850_BIN, col) > 0
THEN PATINDEX(N'%[^ !"$-_a-z{-~]%' collate  SQL_Latin1_General_CP850_BIN, col) 
ELSE CHARINDEX(N'^' collate  SQL_Latin1_General_CP850_BIN, col)
END AS pos
FROM @T;

退货：

xyz[123]}'  0
abc╢123     4
abc௹123     4
def#456     4
def^456     4