sql server - TSQL matching as many comma-separated tags as possible

Question

A table contains a Title field and a Tags field. The tags are generated via latent Dirichlet allocation (LDA) from documents and can be e.g. 'fish, oven, time', 'BBQ, beer' or ' meat, BBQ'. The length of the tags is not fixed.

Given a set of tags, how to find the record with the maximum amount of tags matching no matter the order of the tags?

So, if 'BBQ, meat' is given the best result should be 'meat, BBQ'. If 'BBQ, fish, cream' is given all three records can be returned (they all have one matching tag).

Answer 1

Use this function and Create this one

    CREATE FUNCTION dbo.getCountOfMatch ( @mainString VARCHAR(MAX),  @searchString nvarchar(max))
    RETURNS
      INT
    AS
    BEGIN
        DECLARE @returnCount INT

        SELECT 
            @returnCount = COUNT(1)
        FROM 
            splitstring(@mainString) A INNER JOIN 
            splitstring(@searchString) B ON A.Name = B.Name

        RETURN @returnCount
    END

and

    SELECT TOP 1 // What you want
      Title,
      Tags
    FROM
    (
        SELECT
            A.Title,
            A.Tags,
            dbo.getCountOfMatch(A.Tags, @search) CountTags -- The number of matches.
        FROM
            TABLE A
    ) B
    ORDER BY B.CountTags DESC

UPDATED

DECLARE @searchText NVARCHAR(MAX) = 'BBQ, meat'
DECLARE @query NVARCHAR(MAX) = '
        SELECT
            *
        FROM
            Table
        WHERE '

SELECT
    @query += 
    (
        SELECT
            'Tags like ''%' + A.Name + '%'' AND ' -- Dont forget trim!
        FROM 
            splitstring(@searchText) A
        FOR XML PATH ('')
    )

SELECT @query = LEFT(@query, LEN(@query) - 4) + 'ORDER BY LEN(Tags)' -- For exactly matching: LEN(Tags) = LEN(@searchText)

EXEC sp_executesql  @query

Query look like;

    SELECT
        *
    FROM
        Table
    WHERE 
        Tags like '%BBQ%' AND 
        Tags like '%meat%'
    ORDER BY LEN(Tags)