CODEWITHSUNDEEP
sqlsql-server
AnthonyFG
AnthonyFG

Reputation: 68

How to find out if a search string is in a few columns

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 + '%')

I had the "NOT" next to the "LIKE" and that didn't work either. Also I want to be sure to be case insensitive is this right? I am looking for all the documents from A that the value A.someCol doesn't appear anywhere in B.column1 or B.column2

For example

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

The above should return the row with those values.

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

The above should not return the row with those values.

Upvotes: 0

Views: 43

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use CHARINDEX to solve this:

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

You can also specify to search case-sensitive or case-insensitive:

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

How does it deal with NULLs?

If either expressionToFind or expressionToSearch is NULL, CHARINDEX returns NULL.

In this case you can use one of the following solutions, using 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

or...

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

Upvotes: 1

Related Questions