compareStrings.sql

CREATE FUNCTION dbo.CompareString
(
    @keyString NVARCHAR(MAX),
    @ansString NVARCHAR(MAX),
    @Normalised BIT = 0
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @CompareString NVARCHAR(MAX) = N'+[';
    DECLARE @Delimiter NVARCHAR(1) = ' ';
    
    IF @Normalised = 0
    BEGIN
        SET @keyString = LOWER(LTRIM(RTRIM(@keyString)));
        SET @ansString = LOWER(LTRIM(RTRIM(@ansString)));
    END
    
    DECLARE @keyTable TABLE (Value NVARCHAR(MAX));
    DECLARE @ansTable TABLE (Value NVARCHAR(MAX));
    
    INSERT INTO @ansTable (Value)
    SELECT value
    FROM STRING_SPLIT(@ansString, @Delimiter);

    INSERT INTO @keyTable (Value)
    SELECT value
    FROM STRING_SPLIT(@keyString, @Delimiter);

    -- Compare and build the result
    SET @CompareString = @CompareString + STUFF(
        (SELECT CASE WHEN CHARINDEX(LTRIM(RTRIM(Value)), @keyString) = 0 THEN ' ' + Value ELSE '' END
         FROM @ansTable
         FOR XML PATH('')), 1, 1, '');

    SET @CompareString = @CompareString + N' ] -[';

    SET @CompareString = @CompareString + STUFF(
        (SELECT CASE WHEN CHARINDEX(LTRIM(RTRIM(Value)), @ansString) = 0 THEN ' ' + Value ELSE '' END
         FROM @keyTable
         FOR XML PATH('')), 1, 1, '');

    SET @CompareString = @CompareString + N' ]';

    RETURN @CompareString;
END;