Function for word by word string comparison in T-SQL

Question

I need to write a SQL Server function that will take in two fields and perform a logical operation in this manner
- take first field value, split it into words
- make sure all of these words exist in the second field.

For example: the first field of le chien and the second is le chien joue avec la balle The function would return true in this case, since all the words from the first one are in the second.

I could simply use SUBSTRING:

SUBSTRING(field1, 0, 5)=SUBSTRING(field2, 0,5)

But in some cases it would not work; if I have field1 as la maison (1) and field2 as la maison (2) SUBSTRING(field1, 0, 5)=SUBSTRING(field2, 0,5) would return true even though the strings do not match.

Increasing number of characters in some cases would work, but would not in others. So it all boils down to writing a function that will split the first string into separate words and ensure that all of them exist in the second string.

Can someone help me build a function or point me in the right direction? I'd really appreciate any help

Answer 1

You can use CHARINDEX if you want to see if value1 exists in value2. https://msdn.microsoft.com/en-us/library/ms186323.aspx

IF CHARINDEX(value1, value2) > 0  --found

If the words in value 1 can be in any order in value 2, then you can use the split function that JamesZ linked to in your question. Once you add that function to your database you can use

IF EXISTS(SELECT  * 
          FROM    dbo.DelimitedSplit8K(@value1, ' ')
          WHERE CHARINDEX(item, @value2 ) = 0) -- not found

Answer 2

can you check whether the following solution will fit your requirement

http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END


----Select Statement
SELECT a.splitdata
INTO #firstSet
FROM fnSplitString('le chien', ' ') a

SELECT b.splitdata
INTO #secondSet
FROM fnSplitString('le joue avec la balle', ' ') b

DELETE a 
FROM #firstSet a
JOIN #secondSet b ON a.splitdata = b.splitdata

SELECT * FROM #firstSet


http://sqlfiddle.com/#!6/c5035