SQL Server LIKE with square brackets works directly but not using variable

Question

I am using square brackets in the like to indicate a range of wildcard characters. The weird thing is that using the expression directly in the statement works correctly, fetching the expected rows. But using the same expression from a variable does not bring any results. Why doesn't it work from the variable?

Works:

SELECT * FROM dbo.Table WHERE Column LIKE '%B[A-Z][A-Z]%';
Results: BAA, BAB, BAC, ... until BZZ

Does'n work:

DECLARE @var VARCHAR(10)
SET @var = N'B[A-Z][A-Z]'
SELECT * FROM dbo.Table WHERE Column LIKE CONCAT('%', @var, '%');

I also tried concatenating with + but still getting zero results (LIKE '%' + @var + '%')

The same expression works when I use only one range in the variable, but I really need to use two ranges

SET @var = N'B[A-Z]'
SELECT * FROM dbo.Table WHERE Column LIKE '%' + @var + '%';
Results: BA, BB, BC, BD, ....

question from:https://stackoverflow.com/questions/66062924/sql-server-like-with-square-brackets-works-directly-but-not-using-variable

Answer 1

Make your variable longer so that it can store the whole pattern. You can see what value the variable actually has with PRINT @var, which will show you the closing square bracket has been dropped.

declare @var varchar(10) = 'B[A-Z][A-Z]'
print @var