SQL LIKE not/^ 运算符和多个字符
所以..我以为我很了解mssql，但是今天问我的这个查询让我很困惑。基本上，某处的一段代码使用 LIKE 子句来确定如何过滤某些值。我必须指出，这是无法更改的，我们无法修改查询以使用 NOT LIKE。但如果这就是我想做的事怎么办？考虑这个例子：
select * from
(
select '1.2A' as num
union
select '1.3A' as num
union
select '1.4A' as num
union
select '1.4B' as num
union
select '1.5A' as num
) as test
where num like '1.[^4]_'
现在..如何修改 WHERE 以返回除 1.4A 之外的所有内容？由于显而易见的原因，您不能执行 [^4A] 甚至 [^4][^A]。如果不使用 NOT LIKE 就根本不可能做到这一点吗？还是我在这里遗漏了一些东西，而周五却没有意识到这一点？
So.. I thought I knew mssql fairly well but this one query asked of me today has me perplexed. Basically a piece of code somewhere uses the LIKE clause to determine how to filter certain values. I must point out that this cannot be changed, we cannot modify the query to use NOT LIKE. But what if that's what I want to do? Consider this example:
select * from
(
select '1.2A' as num
union
select '1.3A' as num
union
select '1.4A' as num
union
select '1.4B' as num
union
select '1.5A' as num
) as test
where num like '1.[^4]_'
Now.. how can the WHERE be modified to return everything except 1.4A? You can't do [^4A] or even [^4][^A] for obvious reasons. Is this something which is simply impossible to do without using NOT LIKE? Or am I missing something here and just too Friday'd up to realise it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果你只能改变LIKE之后的表达式，那么你不能用简单的LIKE来做到这一点。
你至少不需要“喜欢”或“或”。您需要正则表达式的匹配能力，这在 SQL Server 中不容易获得。
If you can only change the expression after LIKE, then you can't do this with simple LIKE.
You'd need NOT LIKE or OR at least. You'd need the matching power of Regular Expression which is not available easily in SQL Server.
SQL注入代码安全吗？因为我认为注射是做到这一点的唯一方法。通常，只有
_和%在LIKE操作数中有意义。Is the code SQL-injection safe? Because I see injection as the only way to do that. Usually, only
_and%have a meaning in theLIKEoperands.如果您确实只是想排除 4A，那么：
If it really is just the 4A you're looking to exclude, then: