CODEWITHSUNDEEP
sqlsql-serversql-like
user1605665
user1605665

Reputation: 4151

How do I use a wild card in the middle of an sql server like query

When doing a select in SQL Server i can put a % at the start or end of the queries text to indicate I want any number of characters of other text, but im not sure how to do it in the middle.

e.g. I want SQL like below, 

select *
From Table
Where name like '%[Error] Something failed in (%) session%'

That when ran against the below data

Row | Date      | Log Message
1   |2016-01-01 |'[Error] Something failed in (Freds) session'
2   |2016-01-01 |'[Error] Something failed in (Ilenes) session'
3   |2016-01-01 |'[Error] Something failed in (Freds) session'; Some other warning
4   |2016-01-01 |'[Warning] Something else went wrong'
5   |2016-01-01 |'[Warning] Some other warning'

Would give me 

Row |Date       | Log Message
1   |2016-01-01 |'[Error] Something failed in (Freds) session'
2   |2016-01-01 |'[Error] Something failed in (Ilenes) session'
3   |2016-01-01 |'[Error] Something failed in (Freds) session'; Some other warning

But instead it gives nothing back, what do i need to change.

Upvotes: 2

Views: 2630

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

Problem is due the presence of [] in string. [] is used with LIKE operator to find

any single character within the specified range ([a-f]) or set ([abcdef]).

so you need to ESCAPE the square bracket's 

select 1 
where '[Error] Something failed in (Freds) session'
like '%\[Error] Something failed in (%) session%' escape '\'

or

select 1 
where '[Error] Something failed in (Freds) session'
like '%[[]Error] Something failed in (%) session%'

Upvotes: 5

Related Questions