How to group news based on tags, categories, countries using MS SQL

Question

I have a news table that has various fields like Title, Category, Tag, Country, details ...

I need to show related newsbased on what the user is reading right now.

related news may be based onTag, Category & Country

The problem that I am facing is that TagsI need to search every tag with all the news, for example, if there are 3 tags in the news, then he must search all the news records three times to get the result based on the tags that match.

I set the sample table structure to Fiddle http://sqlfiddle.com/#!18/d9dd7/1

Sample table

CREATE TABLE [TestTable](
    [AutoID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NULL,
    [Category] [nvarchar](50) NULL,
    [Tags] [nvarchar](200) NULL,
    Country [nvarchar](50)
) 

Example

Let's say i read This is News One America

This is News five, " , ", ,

shoule

• 1

SP

DECLARE @Category NVARCHAR(100);
DECLARE @Country NVARCHAR(100);
DECLARE @Tags NVARCHAR(4000);


    SELECT 
    ,@Category = 'Politics'
    ,@Country = 'US'
    ,@Tags = 'Donald Trump, Iran'
    FROM TestTable WHERE AutoID=1

    DECLARE @tempTable TABLE
    (
        TempNewsId INT IDENTITY(1,1),
        ID INT,
        Title NVARCHAR(MAX),
        Desc NVARCHAR(MAX),
        Tags NVARCHAR(100),
        Country NVARCHAR(50),
        Category NVARCHAR(100)
    )

    IF @Category IS NOT NULL
        BEGIN
            SET @Category ='%'+ @Category+'%'
            INSERT INTO @tempTable
            SELECT TOP 4
            AutoID, Title, Desc,Tags,Country,Category FROM TestTable 
            WHERE Category IS NOT NULL AND Category LIKE @Category
        END 

    IF @Tags IS NOT NULL
        BEGIN
            SET @Tags ='%'+ @Tags+'%'
            INSERT INTO @tempTable
            SELECT TOP 4
            AutoID, Title, Desc,Tags,Country,Category FROM TestTable 
            WHERE Tags IS NOT NULL AND Category LIKE @Tags
        END 




    IF @Country IS NOT NULL
        BEGIN
            SET @Country ='%'+ @Country+'%'
            INSERT INTO @tempTable
            SELECT TOP 4
            AutoID, Title, Desc,Tags,Country,Category FROM TestTable 
            WHERE Country IS NOT NULL AND Country LIKE @Country
        END 

    SELECT TOP 20 ID, Title,[Desc],Tags,Country,Category    FROM @tempTable 
    ORDER BY TempNewsId ASC

Answer 1

, , .

create FUNCTION dbo.CompareTags
(
    @inputTagList1 varchar(255),
    @inputTagList2 varchar(255),
    @SplitOn varchar(5) = ','
)
RETURNS int
BEGIN

    declare @tagList1 table (Tag varchar(100));
    declare @tagList2 table (Tag varchar(100));

    -- populate table with first list of tags
    DECLARE @split_on_len INT = LEN(@SplitOn)
    DECLARE @start_at INT = 1
    DECLARE @end_at INT
    DECLARE @data_len INT
    WHILE 1=1
    BEGIN
        SET @end_at = CHARINDEX(@SplitOn,@inputTagList1,@start_at)
        SET @data_len = CASE @end_at WHEN 0 THEN LEN(@inputTagList1) ELSE @end_at-@start_at END
        INSERT INTO @tagList1 (Tag) VALUES( lower(rtrim(ltrim(SUBSTRING(@inputTagList1,@start_at,@data_len)))) );
        IF @end_at = 0 BREAK;
        SET @start_at = @end_at + @split_on_len
    END

    -- populate table with second list of tags
    set @start_at = 1
    WHILE 1=1
    BEGIN
        SET @end_at = CHARINDEX(@SplitOn,@inputTagList2,@start_at)
        SET @data_len = CASE @end_at WHEN 0 THEN LEN(@inputTagList2) ELSE @end_at-@start_at END
        INSERT INTO @tagList2 (Tag) VALUES( lower(rtrim(ltrim(SUBSTRING(@inputTagList2,@start_at,@data_len)))) );
        IF @end_at = 0 BREAK;
        SET @start_at = @end_at + @split_on_len
    END


    -- compare tables
    RETURN (select count(*) from @tagList1 as a inner join @tagList2 as b on a.Tag = b.Tag)
END
go