SQL Server - LIKE [XXXX]

Question

I'm trying to return only values which contain the text [XXXX] in a specific field. My query is returning values which have 4 digit numbers, as I assume SQL server is treating [XXXX] as any number with 4 digits.

I have a test query below with values list to demonstrate the problem.

select X.VadDesc 
From (VALUES
('Product AAAA Description'), 
('Product BBBB Description'), 
('Product [XXXX] Description'), 
('Product2 [XXXX] Description'), 
('Product 2 [AAAA] Description'), 
('Product X 1234 Description'),
('Product X 1235 Description'),
('Product X 1236 Description'),
('Product X 1237 Description')
) as X (VadDesc)
Where X.VadDesc like '%[XXXX]%'

My query should only return:

Product [XXXX] Description
Product2 [XXXX] Description

But it returns:

Product [XXXX] Description
Product2 [XXXX] Description
Product X 1234 Description
Product X 1235 Description
Product X 1236 Description
Product X 1237 Description

How can I do this? I don't just want to look for XXXX as this this may be in rows I don't want to return.

Answer 1

Use an Escape character to escape [ like this '%\[XXXX\]%' {escape '\'}.

Query

select X.VadDesc 
From (VALUES
('Product AAAA Description'), 
('Product BBBB Description'), 
('Product [XXXX] Description'), 
('Product2 [XXXX] Description'), 
('Product 2 [AAAA] Description'), 
('Product X 1234 Description'),
('Product X 1235 Description'),
('Product X 1236 Description'),
('Product X 1237 Description')
) as X (VadDesc)
Where X.VadDesc like '%\[XXXX\]%' {escape '\'}

SQL Fiddle

Answer 2

select X.VadDesc 
From (VALUES
('Product AAAA Description'),  
('Product BBBB Description'),  
('Product [XXXX] Description'),  
('Product2 [XXXX] Description'),  
('Product 2 [AAAA] Description'),  
('Product X 1234 Description'),  
('Product X 1235 Description'),  
('Product X 1236 Description'),  
('Product X 1237 Description')  
) as X (VadDesc) 
Where X.VadDesc like '%[[]XXXX]%'