SQL Server LIKE operator not matching values containing square brackets

Question

I have a table in my database where I inserted all table names in my database. For example I have table name [test].[TestTable] and when I run this query

select * from Tables where Name like N'%[test].[TestTable]%' 

it doesn't return any value but this query works:

select * from Tables where Name like N'%[TestTable]%'

Can someone explain me why?

Answer 1

You have to escape the left square bracket:

SELECT * FROM Tables WHERE Name LIKE N'%[[]test].[[]TestTable]%' 
-----------------------------------------^        ^
--------------------------------------------------+