Check if string is any combination of multiple word combinations saved in database-CodePudding

I have SQL Server database and application using EF. I want to check a list of word combinations as validation before proceeding with certain functionality.

Table in DB:

WordCombination
%lorem%ipsum%
...

I have the %lorem%ipsum% in the DB and want to check a string in the .NET application for likeness against it and get boolean result for example "ipsum lorem" should return TRUE

Currently I have

context.Table.Select(row=>row.WordCombination).Any(combination=>EF.Functions.Like(myString,combination))

notice I use the combination from the DB as a pattern

I try to make the solution as optimal as possible so I don't want to enumerate the table since it may have many rows and I don't want to add any duplicates. Also i try to get only the combination of the two so separating the entry in "lorem" and "ipsum" is not a variant for now.

Is there a way to find both "lorem ipsum","ipsum lorem" and other combinations like "ipsum1234lorem" without adding "ipsum%lorem" entry and without enumerating the table? (with just a query)

Thanks in advance.

CodePudding user response：

Wouldn't the following work?

WHERE (YourColumn LIKE '%lorem%' AND YourColumn LIKE '%ipsum%')
OR YourColumn LIKE '%asdf%'

CodePudding user response：

You could use a stored procedure like below:

ALTER PROCEDURE proc_MatchWordCombinations
    @input NVARCHAR(MAX)
AS
BEGIN
    DECLARE searchRows CURSOR
    FOR SELECT * FROM WordCombinations;

    DECLARE @searchRow NVARCHAR(MAX)

    DECLARE @searchParts TABLE(RowID int not null primary key identity(1,1), searchPart NVARCHAR(MAX), processed BIT NOT NULL DEFAULT 0)
    DECLARE @searchPart NVARCHAR(MAX)
    DECLARE @searchPartID INT

    DECLARE @partsToProcess INT
    DECLARE @index INT
    DECLARE @match BIT

    OPEN searchRows
    
    FETCH NEXT FROM searchRows INTO @searchRow

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Search row: '   @searchRow

        DELETE FROM @searchParts

        INSERT INTO @searchParts (searchPart)
        SELECT value
        FROM STRING_SPLIT(@searchRow, '%');
        DELETE FROM @searchParts WHERE searchPart = ''

        SELECT @partsToProcess = COUNT(1) FROM @searchParts
        SET @index = 0
        SET @match = 1
        WHILE @index < @partsToProcess
        BEGIN
            SET @index = @index   1
            PRINT @index
            
            SELECT TOP 1 @searchPart = searchPart, @searchPartID = RowID FROM @searchParts WHERE processed = 0
            UPDATE @searchParts SET processed = 1 WHERE RowID = @searchPartID
            PRINT 'Search part: '   @searchPart

            IF CHARINDEX(@searchPart, @input) = 0
            BEGIN
                SET @match = 0
                PRINT 'No match: '   @searchPart
            END
            ELSE
            BEGIN
                PRINT 'Match: '   @searchPart
            END
        END

        IF @match = 1
        BEGIN
            -- The input matches the search row
            PRINT 'Match on: '   @searchRow
        END

        FETCH NEXT FROM searchRows INTO @searchRow
    END

    CLOSE searchRows
    DEALLOCATE searchRows
END

And call it like so:

EXEC proc_MatchWordCombinations @input = 'ipsum1234lorem'