SQL 比较一个列与另一列相似的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4700550/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare Columns Where One is Similar to Part of Another
提问by craigmj
I'm trying to write a Select statement where I can see if one column is like part of another.
我正在尝试编写一个 Select 语句，我可以在其中查看一列是否像另一列的一部分。
tblNames
ID FullName FirstName
1 Mr. John Doe, CEO John
2 Mr. Jake Doe, Exec Jake
3 Mrs. Betty Smith, Chair Jill
The query should return:
查询应返回：
3 | Mrs.Betty Smith, Chair | Jill
However mine just returns every row in the table:
但是我的只是返回表中的每一行：
SELECT ID, FullName, FirstName
FROM tblNames
WHERE '%' + FirstName + '%' not like Fullname
Any ideas?
有任何想法吗？
回答by jzd
Reverse the where, to something like this:
将 where 反转为如下所示：
Fullname not like '%' + FirstName + '%'
回答by p.campbell
Try this:
尝试这个：
SELECT * FROM tblNames
WHERE ISNULL( CHARINDEX (FirstName , FullName),0) = 0
The CHARINDEXwill be faster (more performant) than a LIKEclause, as it doesn't have to take wildcards into account. The sample data above with small numbers of rows won't show a performance benefit, but when in the millions of rows, CHARINDEXwould perform better.
该CHARINDEX会更快（更高性能）比一个LIKE条款，因为它没有把通配符考虑。上面带有少量行的示例数据不会显示性能优势，但在数百万行时，性能CHARINDEX会更好。
回答by Jeroen
This worked for me:
这对我有用：
SELECT *
FROM `table`
WHERE `col1` NOT LIKE CONCAT('%', `col2`, '%')
Found it here: http://www.edmondscommerce.co.uk/mysql/compare-two-columns-in-mysql/
在这里找到：http: //www.edmondscommerce.co.uk/mysql/compare-two-columns-in-mysql/
Somehow it only worked correctly with the concat-function (?).
不知何故，它只能与 concat-function (?) 一起正常工作。
回答by WReach
Switch the arguments to LIKEin the WHEREclause:
LIKE将WHERE子句中的参数切换到：
SELECT ID, FullName, FirstName
FROM tblNames
WHERE Fullname not like '%' + FirstName + '%'
The wildcard must be the second argument.
通配符必须是第二个参数。
回答by Jon
It looks OK, except you probably want to switch the order around in your where:
看起来不错，除了您可能想在您的位置切换顺序：
WHERE Fullname not like '%' + FirstName + '%'
回答by Nwaokoro jacinta chinyere
Oracle expects number when + is used. For string, please use the samle below :
使用 + 时，Oracle 需要数字。对于字符串，请使用以下示例：
SELECT ID, FullName, FirstName
FROM tblNames
WHERE FullName like '%' || FirstName||'%'
To compare one column of a table to a column of another table, please do the following
要将表的一列与另一表的列进行比较，请执行以下操作
select a.*,table_2_col_1, table_2_col_2 from (select table_1_col_1, table_1_col_2 from table_1 where
) a, table_2 where table_1_col_1 like '%' || table_2_col_1 ||'%'
回答by Zlosk
Parentheses also would have fixed the issue.
括号也可以解决这个问题。
SELECT ID, FullName, FirstName
FROM tblNames
WHERE ('%' + FirstName + '%') not like Fullname
![WHERE col1,col2 IN (...) [使用复合主键的 SQL 子查询]](/res/img/loading.gif)