如何在几列中找出搜索字符串是否为

Question

SELECT *
FROM [FirstTable] as A
JOIN [SecondTable] as B on A.ID = B.ID
WHERE (NOT B.column1 LIKE '%'+ A.someCol + '%')
AND (NOT B.column2 LIKE '%'+ A.someCol + '%')

我旁边有"不是"的"不是"，但也没有起作用。我也想确保案件不敏感吗？我正在寻找A.Somecol值A.somecol在b.column1或b.column2

中没有出现的所有文档

例如

A.someCol 'substr'
B.column1 'test'
B.column2 'test2'

以上应用这些值返回行。

A.someCol 'substr'
B.column1 'test'
B.column2 'testsubstrtest'

以上不应用这些值返回行。

Answer 1

您可以使用CHARINDEX解决此问题：

SELECT *
FROM [FirstTable] as A
JOIN [SecondTable] as B ON A.ID = B.ID
WHERE CHARINDEX(A.someCol, B.column1) = 0 
    AND CHARINDEX(A.someCol, B.column2) = 0

您也可以指定搜索案例敏感或不敏感的情况：

SELECT *
FROM [FirstTable] as A
JOIN [SecondTable] as B ON A.ID = B.ID
WHERE CHARINDEX(A.someCol, B.column1 COLLATE Latin1_General_CI_AS) = 0 
    AND CHARINDEX(A.someCol, B.column2 COLLATE Latin1_General_CI_AS) = 0

---

它如何处理nulls？

如果 expressionTofind 或 expressionTosearch 为null， CHARINDEX返回null。

在这种情况下，您可以使用ISNULL：

使用以下解决方案之一

SELECT *
FROM [FirstTable] as A
JOIN [SecondTable] as B ON A.ID = B.ID
WHERE ISNULL(CHARINDEX(A.someCol, B.column1), 0) = 0 
    AND ISNULL(CHARINDEX(A.someCol, B.column2), 0) = 0

或...

SELECT *
FROM [FirstTable] as A
JOIN [SecondTable] as B ON A.ID = B.ID
WHERE CHARINDEX(ISNULL(A.someCol, ''), ISNULL(B.column1, '')) = 0 
    AND CHARINDEX(ISNULL(A.someCol, ''), ISNULL(B.column2, '')) = 0