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)
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…