A simple question came up on a forum the other day. The poster was trying to work out why he couldn't find square brackets (i.e. [ ] ) using LIKE in T-SQL.
The trick is that to find the opening bracket, you need to enclose it in a pair of square brackets. But you can just find the closing one directly.
Let's see an example. I'll create a table and populate it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE tempdb; GO DROP TABLE IF EXISTS dbo.TestTable; CREATE TABLE dbo.TestTable ( TestTableID int PRIMARY KEY, TestValue nvarchar(50) ); GO INSERT dbo.TestTable (TestTableID, TestValue) VALUES (1, N'Goodbye is the [hardest] word'), (2, N'[Listen now]'), (3, N'No brackets here'), (4, N'This is the end ]'); GO |
And now we'll query it. Note that using a single opening bracket does not work:
1 2 3 4 |
SELECT * FROM dbo.TestTable WHERE TestValue LIKE N'%[%'; GO |
That returns no rows.
But a query for the closing bracket is a different story:
1 2 3 4 |
SELECT * FROM dbo.TestTable WHERE TestValue LIKE N'%]%'; GO |
That returns rows 1, 2, and 4 as expected.
Now let's query for the opening bracket:
1 2 3 4 |
SELECT * FROM dbo.TestTable WHERE TestValue LIKE N'%[[]%'; GO |
And note in the main image above, that it works as expected.