sql - 如何在类似 sql server 的查询中间使用通配符

Question

在 SQL Server 中进行选择时，我可以在查询文本的开头或结尾放置一个 % 来指示我想要任意数量的其他文本字符，但我不确定如何在中间执行此操作。

例如我想要像下面这样的 SQL，

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

当针对以下数据运行时

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'

会给我

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

但是它没有任何返回，我需要改变什么。

Answer 1

问题是由于字符串中存在 [] 造成的。 [] 与 LIKE 运算符一起使用来查找

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

所以你需要ESCAPE方括号的

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

或

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

• SQL FIDDLE DEMO