How to show only rows where there is no match between strings at all?

user_11479825 0
Viewed 170

I have to compare two columns and be able to extract only rows where there is not even a partial match between strings. For example, I have this table:

Col1 Col2
John Smith John Smith ltd
Pepper Row Whatever Pepper
red Blue

The only row I should be seeing after the filtration is the last one:

Col1 Col2
red Blue

I found an answer here which shows results with partial matches. I tried to modify it to return only 0 matches by using NOT LIKE but it did not work out.

string-matching sql-server sql
2 Answers

If you want rows where none of the words overlap, you can use a not exists clause:

select t.*
from t
where not exists (select 1
                  from string_split(t.col1, ' ') s1 join
                       string_split(t.col2, ' ') s2
                       on s1.value = s2.value
                 );

Note: This formulation allows you to return the entire row -- that is, other columns that are not included in the comparison.

If you are using an older version of SQL Server that does not support string_split(), I would suggest finding the code for a user-defined function that does the same thing.

user_11440350

If you just want to split col1 words and want to check if it is present in the col2 or not, for this you can try like following.

select col1,col2 from mytable
except   
select col1,col2 from mytable t
cross apply
(
 select * from string_split(t.Col1,' ')
)x    
where col2  like  '%' + [value] + '%'

Note: string_split will work on SQL Server 2017+, if you are using older version you need to use some custom split function.

user_2973220
Related