SQL Query to Get Common Words from Multiple Strings or Sentences - SQL Server 2008

Here I will explain how to get common words from multiple strings or sentences using SQL Query in SQL Server 2008/12.

 Example

Sentence 1: ASP.NET solutions Center

Sentence 2: we are providing solutions on ASP.NET

Sentence 3: I like ASP.NET solutions

sentence 4: Best web solutions using ASP.NET

The result should be: ASP.NET solutions

Based on SQL Server version Configuration, you will need a split  function that can split a string on a different delimiter(LIKE Space,Common) of choice. Here's such a function.

CREATE FUNCTION [dbo].[uf_Split]
(
@InputData NVARCHAR(MAX),
@Delimiter NVARCHAR(5)
)
RETURNS @table TABLE (ID int IDENTITY(1,1), data NVARCHAR(MAX), descriptor varchar(255) NULL)
AS 
BEGIN

DECLARE @textXML XML;

SELECT @textXML = CAST('<s>'+ REPLACE(@InputData, @Delimiter, '</s><s>' ) + '</s>' AS XML); 

INSERT INTO @table(data) 
SELECT RTRIM(LTRIM(T.split.value('.', 'nvarchar(max)'))) AS data FROM @textXML.nodes('/s') T(split) 

 RETURN 
END

Following are some cases to get common words using split function by executing sql query

CASE - 1 (Get Common Words from two Sentence)

SELECT 
sentence1.data FROM dbo.uf_Split('ASP.NET solutions Center',' ') sentence1
INNER JOIN dbo.uf_Split('we provide solutions on web technologies ',' ') sentence2
ON sentence1.data = sentence2.data

After execute above query we got following result:

CASE - 2 (Get Common Words from three Sentence)

SELECT 
sentence1.data FROM dbo.uf_Split('ASP.NET solutions Center',' ') sentence1  
INNER JOIN dbo.uf_Split('we are providing web solutions on ASP.NET',' ')
sentence2 ON sentence1.data = sentence2.data
INNER JOIN dbo.uf_Split('I like ASP.NET Solutions',' ') sentence3
ON sentence2.data = sentence3.data
order by data

 
After execute above query we got following result:

CASE - 3 (Get Common Numbers From different group of Numbers per Sentence and for that we need to use comma as delimiter to split numbers)

SELECT 
sentence1.data FROM dbo.uf_Split('145,107,454,687',',') sentence1 INNER JOIN dbo.uf_Split('569,456,454,258,321,107,1513',',') sentence2
ON sentence1.data = sentence2.data
INNER JOIN dbo.uf_Split('5231,789,489,687,107,454,9631',',') sentence3
ON sentence2.data = sentence3.data
INNER JOIN dbo.uf_Split('852,454,896,5241,6589,107',',') sentence4 ON sentence3.data = sentence4.data
order by data

After execute above query we got following result: