SQL query to return whether two rows contain a generic term

Question

Is there an easy way to check in a SQL query if two string values ​​from different columns have a term?

For instance:

Str1 = "little brown fox" Str2 = "big brown bear" Return_Value = 1 Str1 = "Sun is shinning" Str2 = "Its raining" Return_Value = 0 

Answer 1

This is probably not the best user-defined feature, but it can help you along the path of what you need.

 CREATE FUNCTION [dbo].[ContainsSharedTerm] ( @SearchString1 varchar(255), @SearchString2 varchar(255) ) RETURNS BIT AS BEGIN DECLARE @MatchFound BIT SET @MatchFound = 0 DECLARE @TempString VARCHAR(255) WHILE LEN(@SearchString1) > 0 AND @MatchFound = 0 BEGIN IF CHARINDEX(' ',@SearchString1) = 0 BEGIN SET @TempString = @SearchString1 SET @SearchString1 = '' END ELSE BEGIN SET @TempString = LEFT(@SearchString1,CHARINDEX(' ',@SearchString1)-1) SET @SearchString1 = RIGHT(@SearchString1,LEN(@SearchString1)-CHARINDEX(' ',@SearchString1)) END IF CHARINDEX(@TempString,@SearchString2) > 0 BEGIN SET @MatchFound = 1 END END RETURN @MatchFound END CREATE TABLE #TestTable ( Col1 VARCHAR(100), Col2 VARCHAR(100) ) INSERT INTO [#TestTable] ([Col1],[Col2]) VALUES ('little brown fox','big brown bear') INSERT INTO [#TestTable] ([Col1],[Col2]) VALUES ('Sun is shinning','Its raining') SELECT [Col1],[Col2],dbo.[ContainsSharedTerm]([Col1],[Col2]) AS [Match] FROM [#TestTable] DROP TABLE [#TestTable] 

Answer 2

You may need to make some changes to the lines you are comparing, but I would recommend looking at the CONTAINS function

Example:

 WHERE CONTAINS('little brown fox', 'big OR brown OR bear') OR CONTAINS('Sun is shinning', 'Its OR raining') 

You can learn more about CONTAINS here:

http://msdn.microsoft.com/en-us/library/ms187787.aspx

Answer 3

You did not specify your DBMS, but with PostgreSQL it will be something like this:

 select count(*) from ( select unnest(string_to_array('little brown fox', ' ')) intersect select unnest(string_to_array('big brown bear', ' ')) ) t 

will return the number of words matching between both lines (so 1 for the above example and 0 for your second example)