Oct 17, 2014

How to find common words between two SQL variables

MSSQL How to find common words between two sentences:
CREATE FUNCTION [dbo].[EscapeXml](@data NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
 DECLARE @t NVARCHAR(MAX)
 --< (<), & (&), > (>), " ("), and ' (')
 SELECT @t = REPLACE(REPLACE(REPLACE(REPLACE(@data,'<','<'),'&','&'),'"','"'),'''',''')
 
 RETURN @t
END
GO
CREATE FUNCTION [dbo].[fnSplit](@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (rowNum int IDENTITY(1,1), data NVARCHAR(max), descriptor varchar(255) NULL)
AS
BEGIN
    SELECT @data = [dbo].[EscapeXml](@data)
    DECLARE @textXML XML;
    SELECT    @textXML = CAST('' + REPLACE(@data, @delimiter, '') + '' AS XML);

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

    RETURN
END
Usage:
SELECT sentence1.data as common
FROM dbo.fnSplit('This is sentence1 test',' ') sentence1 
INNER JOIN dbo.fnSplit('This is sentence2 test',' ') sentence2 ON sentence1.data = sentence2.data
Output:

common
--------
This
is
test