SQL查询字段不包含$ x的地方SQL Query Where Field DOES NOT Contain $x

- 此内容更新于:2014-12-30
主题:

我想找一个SQL查询来寻找行field1不包含$ x的地方。我怎么能这样做呢?

原文:

I want to find an SQL query to find rows where field1 does not contain $x. How can I do this?

Kate的回复:我可以用包含()函数(sql)同样的检查吗?

(原文:Can I use CONTAINS() function (sql) to make the same check?)

解决方案:
原文:

What kind of field is this? The IN operator cannot be used with a single field, but is meant to be used in subqueries or with predefined lists:

-- subquery
SELECT a FROM x WHERE x.b NOT IN (SELECT b FROM y);
-- predefined list
SELECT a FROM x WHERE x.b NOT IN (1, 2, 3, 6);

If you are searching a string, go for the LIKE operator (but this will be slow):

-- Finds all rows where a does not contain "text"
SELECT * FROM x WHERE x.a NOT LIKE '%text%';

If you restrict it so that the string you are searching for has to start with the given string, it can use indices (if there is an index on that field) and be reasonably fast:

-- Finds all rows where a does not start with "text"
SELECT * FROM x WHERE x.a NOT LIKE 'text%';
解决方案:
从表中选择* field1不像“% $ x %”;(确保你正确逃脱$ x事先避免SQL注入) 编辑:没有一点不同的东西——你的问题并不完全清楚所以选择使用哪一个。像“xxx %”可以使用一个索引。像“xxx %”或“% xxx %”不能。
原文:

SELECT * FROM table WHERE field1 NOT LIKE '%$x%'; (Make sure you escape $x properly beforehand to avoid SQL injection)

Edit: NOT IN does something a bit different - your question isn't totally clear so pick which one to use. LIKE 'xxx%' can use an index. LIKE '%xxx' or LIKE '%xxx%' can't.

Pieter Bos的回复:什么是逃避正常吗?我知道,你只需要正常的字符串逃避一些东西,但像有一些额外的特殊字符。

(原文:What is considered escaping properly? I know that with normal strings you only have to escape a few things, but LIKE has some extra special characters.)