sql - 多个字符与 LIKE 子句匹配

Question

我需要从 SQL Server 表中返回同时包含 _1 和 _2 的行。下面是我的查询，即使上述两个条件都不匹配，它也会返回行。

据我所知，在使用带通配符的 LIKE 子句时，我可以使用 [ 和 ] 来匹配多个值。

DECLARE @TempTable AS TABLE (Col VARCHAR(MAX))

INSERT INTO @TempTable VALUES
('001098E2-5995-446F-87DC-BE71D2B30D37_2|69FA4BCD-C90B-4375-B08C-B3615C55500D_2'),
('0025F9BF-995D-4C88-BBD6-4C707A88BC32_1|F53668A8-F819-4309-BC0C-8DE4C2637419_2'),
('007BB00A-3B69-45FC-A265-1EC8B00E011A_2|0701649E-8BFE-4B03-8456-51E2D9169BD5_2|08950E50-80B2-4BDF-9FC7-3AB0AA4587AE_1')

SELECT * FROM @TempTable
WHERE (Col LIKE '%[_1]%' AND Col LIKE '%[_2]%')

我预计不会返回 001098E2-5995-446F-87DC-BE71D2B30D37_2|69FA4BCD-C90B-4375-B08C-B3615C55500D_2，但 SQL Server 返回所有 3 条记录。

提前致谢。

Answer 1

SELECT * FROM @TempTable
WHERE (Col LIKE '%[_]1%' AND Col LIKE '%[_]2%')

Using Wildcard Characters As Literals

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.