T-SQL LIKE with escaped characters

Question

To match all records with an nvarchar column that contains substring Type [B]

for example : Owner [C] Type [B] Subtype [X]

the following works: LIKE '%Type [[]B[]]%'

But it's rather confusing syntax.

Is there a better alternative ?

Answer 1

You can use ESCAPE option.

The query will be LIKE '%Type #[B]%' ESCAPE '#'

Please find the working query with sample data:

DECLARE @TextTable TABLE (String NVARCHAR (500));
INSERT INTO @TextTable(String) SELECT ('Owner [C] Type [B] Subtype [X]');
INSERT INTO @TextTable(String) VALUES ('Owner [C] Type [B Subtype [X]');
INSERT INTO @TextTable(String) VALUES ('Owner [C] Type B Subtype [X]');

SELECT *
FROM @TextTable
WHERE String LIKE '%Type #[B]%' ESCAPE '#'

Working demo on db<>fiddle