如何在 SQL Server 的 varbinary(max) 字段中搜索字节序列？

Question

我正在尝试在SQL Server 2012上编写一个查询，该查询将返回包含指定字节序列的varbinary(max)列。我能够使用将varbinary字段转换为varchar并使用LIKE的查询来实现这一点：

SELECT * FROM foo
WHERE CONVERT(varchar(max), myvarbincolumn) LIKE 
  '%' + CONVERT(varchar(max), 0x626C6168) + '%'

我的目标字节序列是“0x626C6168”。不幸的是，这只适用于字段不包含任何值为零（0x00）的字节，并且在我的数据中这些字节非常常见。我可以采取不同的方法来处理包含零值字节的值吗？

- Dan Hermann

Answer 1

很不幸，马丁提出的解决方案有一个缺陷。

如果搜索键中的二进制序列包含任何0x25字节，则会将其转换为%字符（根据ASCII表）。

这个字符然后被解释为like语句中的通配符, 导致许多不想要的结果出现。

-- A table with a binary column:
DECLARE @foo TABLE(BinCol VARBINARY(MAX));
INSERT INTO @foo (BinCol) VALUES (0x001125), (0x000011), (0x001100), (0x110000);

-- The search key:
DECLARE @key VARBINARY(MAX) = 0x1125; -- 0x25 is '%' in the ASCII table!

-- This returns ALL values from the table, because of the wildcard in the search key:
SELECT * FROM @foo WHERE
    CONVERT(VARCHAR(max), BinCol) COLLATE Latin1_General_100_BIN2
    LIKE ('%' + CONVERT(VARCHAR(max), @key) + '%');

要解决这个问题，请使用以下搜索子句：

-- This returns just the correct value -> 0x001125
SELECT * FROM @foo WHERE 
    CHARINDEX
    (
        CONVERT(VARCHAR(max), @key),
        CONVERT(VARCHAR(max), BinCol) COLLATE Latin1_General_100_BIN2
    ) > 0;

- sɐunıɔןɐqɐp

Answer 2

如果你使用二进制排序规则，它应该能够正常工作。

WITH foo(myvarbincolumn) AS
(
SELECT 0x00626C616800
)
SELECT *
FROM   foo
WHERE  CONVERT(VARCHAR(max), myvarbincolumn) COLLATE Latin1_General_100_BIN2 
                    LIKE '%' + CONVERT(VARCHAR(max), 0x626C6168) + '%' 

如果您使用较旧版本的SQL Server，则可能需要使用例如Latin1_General_BIN之类的字符集。

- Martin Smith

Answer 3

我刚刚发现了这个非常简单的查询。

SELECT * FROM foo
WHERE CONVERT(varchar(max), myvarbincolumn,2) LIKE '%626C6168%'

对于样式2，转换结果左侧不会添加字符0x。

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#binary-styles

- Michael Riley - AKA Gunny