Search keyword with LIKE %[keyword]%

Question

I have a TransactionDetails table with TransactionId, TransactionDetail columns, those are used to track all the transactions within the application.

The sample records of the TransactionDetail are:

[Blah][Log] 20 Records Inserted
[Blah][Exception] Fails on INSERT INTO
[Blah][Error] Authentication Fails Logged
[Blah][Warning] Null value is eliminated by an aggregate
[Blah][Log] 10 Records Deleted

I want to filter the TransactionDetail columns having the [Log] only (The Log, Logged keyword also exist in the other rows). The search query with [] consider as regular expression pattern. How can I skip that and get the required details only.

I have tried the \ escape character in front of the [, ], but it doesn't return any result.

SELECT *
FROM TransactionDetails
WHERE TransactionDetail LIKE '%\[Log\]%'

Expected result:

[Blah][Log] 20 Records Inserted
[Blah][Log] 10 Records Deleted

SQL Fiddle: http://sqlfiddle.com/#!3/d69f3d/1

How many different Tags can you have? Why not have a seperate column with a key to a table that stores 1, [Log] - 2, [Exception] - 3, [Error] - 4,[Warning] etc... then just join. It would be WAY faster to pull out of the DB saying where keyword = 1 with a left join to get the word log — Daniel E., Feb 09 '16 at 13:42
Try this http://stackoverflow.com/questions/439495/how-can-i-escape-square-brackets-in-a-like-clause — Failwyn, Feb 09 '16 at 13:47
@t-clausen.dk, thanks for the info. (As a non-MS SQL guy it's hard to remember all product specific details...) — jarlh, Feb 09 '16 at 14:36

score 3 · Accepted Answer · answered Feb 09 '16 at 13:42

3

Escape SQL wildcard characters by putting them in the group, i.e.:

SELECT *
FROM TransactionDetails
WHERE TransactionDetail LIKE '%[[]Log]%'

answered Feb 09 '16 at 13:42

Zbynek Vyskovsky - kvr000

18,186
3
35
43

score 2 · Answer 2 · edited May 23 '17 at 11:45

As mentioned by jarlh in the comment the ESCAPE option also works:

SELECT *
FROM TransactionDetails
WHERE TransactionDetail LIKE '%#[Log#]%' ESCAPE '#'

Working Demo: http://sqlfiddle.com/#!3/d69f3d/37

Update: As per the reply by t-clausen.dk in this comment, the escape_character is not required for the ]. So the code below also works.

SELECT *
FROM TransactionDetails
WHERE TransactionDetail LIKE '%#[Log]%' ESCAPE '#'

Search keyword with LIKE %[keyword]%

2 Answers2

Linked