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('Usage:' + 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
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.dataOutput:
common
--------
This
is
test