Ranking with the LIKE keyword in SQL Server

Question

I have a table like

╔═══════════════════╗
║     Title         ║
╠═══════════════════╣
║     Blogroll      ║
║     Bottom Menu   ║
║     Business      ║
║     Entertainment ║
║     extend        ║
╚═══════════════════╝

and my search criteria are similar to

WHERE title LIKE '%blogroller%'

obviously I won’t have a result, but can I find the counter in which the similar sentence ended, as here, in this case its value is above 8, which made the sentence fail?

Any hint would be appreciated.

thank

Answer 1

You can do this, but only with a lot of manual effort:

select title,
       (case when title like '%blogroller%' then 10
             when title like '%blogrolle%' then 9
             when title like '%blogroll%' then 8
             . . .
             else 0
        end) as MatchLen
from table t
order by MatchLen desc;

(Note: in some versions of SQL Server, you may need a subquery to reference MatchLen.)

Answer 2

, , - SQL.

, MAX LIKE. , SQL , , , .

-- test data

DECLARE @token NVARCHAR(100)
SET @token = 'Blogroller'

DECLARE @titles TABLE (Title NVARCHAR(100))

INSERT @titles VALUES
('Blogroll'),
('Bottom Menu'),
('Business'),
('Entertainment'),
('extend')

-- solution

DECLARE @patterns TABLE (part NVARCHAR(100) PRIMARY KEY, tokenLen int)

WHILE (LEN(@token) > 0) 
BEGIN
    INSERT @patterns VALUES (@token, LEN(@token))
    SET @token = SUBSTRING(@token, 1, LEN(@token) - 1)
END

SELECT MAX(patterns.tokenLen)
FROM @titles titles
INNER JOIN @patterns patterns ON titles.Title LIKE '%' + patterns.part + '%'

Answer 3

, :

declare @phrase as varchar(100) = 'blogroller'
declare @match_length as int = 0

while len(@phrase) > 0
begin
    if (select count(title) from titles where title like '%' + @phrase + '%') > 0
    begin
        set @match_length = len(@phrase)
        break
    end

    set @phrase = LEFT(@phrase, len(@phrase) - 1)
end

print @match_length

Answer 4

(master..spt_values, ), , , , :

:

CREATE TABLE Something (Title NVARCHAR(MAX));
INSERT Something (Title)
VALUES ('Blogroll')
,('Bottom Menu')
,('Business')
,('Entertainment')
,('extend')

:

DECLARE @SearchTerm NVARCHAR(MAX);
SET @SearchTerm = 'Blogroller';

WITH CTE_SearchBroken AS 
(
    SELECT LEFT(@SearchTerm, number) BrokenTerm 
    FROM master..spt_values n
    WHERE n.type = 'P' AND n.number <= LEN(@SearchTerm)
)
, CTE_PreliminaryResults AS 
(
    SELECT *, LEN(BrokenTerm) AS BrokenAt 
    FROM Something s 
    LEFT JOIN CTE_SearchBroken b ON s.Title LIKE '%' + b.BrokenTerm + '%'
)
SELECT Title
    , MAX(BrokenAt) AS BrokenAt
    , CASE WHEN LEN(@SearchTerm) = MAX(BrokenAt) THEN 1 ELSE 0 END AS Found
FROM CTE_PreliminaryResults
GROUP BY Title

Answer 5

Based on Gordon's answer, you can write a function that automatically goes through all the possibilities. Also similar to Lakorkin's answer; it may have better performance because it makes good use of the inner join; you can do some tests there.

CREATE FUNCTION dbo.MatchLen(@needle AS varchar(max), @haystack AS varchar(max)) RETURNS int
AS
BEGIN
    DECLARE @len AS int = LEN(@needle)
    WHILE @len > 0 AND @haystack NOT LIKE '%' + REPLACE(REPLACE(REPLACE(LEFT(@needle, @len), '[', '[[]'), '%', '[%]'), '_', '[_]') + '%'
        SET @len = @len - 1
    RETURN @len
END

Example:

SELECT dbo.MatchLen('blogroller', 'This blog has no blogroll, sir!')

returns 8.

Answer 6

Try:

select 
    *,
    LEN(Title) MatchLength
From YourTable
where 'Blogroller' like Title+'%'

OR

select 
    *,
    LEN(Title) MatchLength
From YourTable
where 'Blogroller' like '%'+Title+'%'