Multiple charactor match with LIKE clause

January 21, 2022

I need to return the rows that contain both _1 and _2 from the SQL Server table. Below is my query and it returns the rows even both the above conditions are not matched.

As per my knowledge, I can use [ and ] to match multiple values when using the LIKE clause with wild cards.

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]%')

I expect not to return 001098E2-5995-446F-87DC-BE71D2B30D37_2|69FA4BCD-C90B-4375-B08C-B3615C55500D_2, but SQL Server returns all 3 records.

Thanks in advance.

>Solution :

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.