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 ?

score 3 · Accepted Answer · answered Nov 14 '19 at 02:10

3

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

answered Nov 14 '19 at 02:10

Arulkumar

12,966
14
47
68

Also please refer this post: [Search keyword with LIKE %keyword%](https://stackoverflow.com/q/35293531/2451726) – Arulkumar Nov 14 '19 at 02:14
It's only necessary to escape the open square-bracket `[` but not the close square-bracket `]` ? – BaltoStar Nov 14 '19 at 18:21
@baltostar Yes, `]` does not need escaping. – Arulkumar Nov 15 '19 at 01:40

Vanderlei Morais · Answer 2 · 2019-11-14T02:16:49.410

2

Yes, you can use ESCAPE and define your custom escape character:

select *
from   (select 'Owner [C] Type [B] Subtype [X]' as field) as t
where   
   t.field LIKE '%Type \[B]%' ESCAPE '\' --can be any char
   --t.field LIKE '%Type .[B]%' ESCAPE '.'

edited Nov 14 '19 at 02:16

answered Nov 14 '19 at 02:10

Vanderlei Morais

556
6
11

It's only necessary to escape the open square-bracket `[` but not the close square-bracket `]` ? – BaltoStar Nov 14 '19 at 18:22

T-SQL LIKE with escaped characters

2 Answers2