How do i match text excluding difference between brackets in SQL server?

Question

I've an audit log table that has a series of strings like

"Some Text[details]more text"

the pattern before and after the [details] indicates what the audit trail entry type is. The text in the bracket indicates what it is for. I want to create a query to only find the audit entries i'm after. I thought to use the following like "Some Text[%]more text" but it does not seem to work

When I run the below query it retrieves the expected results + more

select top 1000 *
from Table
where NAME like 'Some Text%'

When I try

select top 1000 *
from Table
where NAME like 'Some Text[%'

Nothing comes back is the

hi, are you trying to search for values in that column excluding the strings inside the open ([) and close (]) brackets? — t1t1an0, Dec 18 '15 at 07:53

score 1 · Answer 1 · answered Dec 18 '15 at 05:26

Brackets have a special syntactic meaning in regular expressions. So you need to escape the bracket if you want to use it in your query:

select top 1000 *
from Table
where NAME like 'Some Text[[]%'

Special characters can be escaped by placing them inside brackets. In this case, the opening bracket itself needs to be placed inside brackets, i.e. [[]

score 1 · Answer 2 · answered Dec 18 '15 at 08:13

try the t-sql code below:

create table dbo.tblTest (ID int IDENTITY(1, 1), strings varchar(200))

insert dbo.tblTest
select 'i have to find this text excluding [these strings inside the brackets]'
union all select '[don''t include these texts inside the brackets]. but include these!'
union all select 'why can''t i search for these, but [not these]? nothing seems to work when brackets are involved. :('

select *
from dbo.tblTest

DECLARE @stringToSearchFor VARCHAR(200) = 'nothing seems'

SELECT t.*
FROM dbo.tblTest t
    JOIN
        (SELECT nobrackets.*
        FROM
            (SELECT cleanString = REPLACE(t.strings, SUBSTRING(t.strings, CHARINDEX('[', t.strings), CHARINDEX(']', t.strings) - CHARINDEX('[', t.strings) + 1), '')
                , t.ID
            FROM dbo.tblTest t) noBrackets
        WHERE noBrackets.cleanString LIKE CONCAT('%', @stringToSearchFor, '%')) tNoBracket ON tNoBracket.ID = t.ID

score 0 · Answer 3 · edited May 23 '17 at 10:27

0

If you will take sometime here in stackoverflow, a lot of post will answer your question.. Please see below.

You need to use [ ] bracket to surround the text with special character..

The query now look something like:

select top 1000 *
from Table
where NAME like '[Some Text[]%'

SQL LIKE CONDITION

SQL Server LIKE containing bracket characters

edited May 23 '17 at 10:27

Community

1
1

answered Dec 18 '15 at 05:26

japzdivino

1,736
3
17
25

score 0 · Answer 4 · edited May 23 '17 at 12:34

0

select top 1000 *
from Table
where NAME like 'Some Text[[%] more text'

or

select top 1000 *
from Table
where NAME like 'Some Text![%] more text' ESCAPE '!'

How can I escape square brackets in a LIKE clause?

edited May 23 '17 at 12:34

Community

1
1

answered Dec 18 '15 at 05:29

Shep

638
3
15

How do i match text excluding difference between brackets in SQL server?

4 Answers4