SQL: Finding square brackets using LIKE in SQL Server T-SQL

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:

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:

SELECT *
FROM dbo.TestTable 
WHERE TestValue LIKE N'%[%';
GO

That returns no rows.

But a query for the closing bracket is a different story:

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:

SELECT *
FROM dbo.TestTable 
WHERE TestValue LIKE N'%[[]%';
GO

And note in the main image above, that it works as expected.

2020-04-02