Text Search on Multiple Columns without enabling FULL TEXT Search

Question

I have a scenario where we need to enable a multi-keyword search within a text field. This search should involve scanning the keywords across several columns (approximately 4-6) in multiple tables (currently 2 tables).

Problem, I can build the query with simple "AND" "OR" operators

1. Query in Attempt 1 : Return more than required output

2. Query in Attempt 2 : Return zero record in output

3. Required OUTPUT: is the only first row from output of attempt 1

    -- truncate table #Customer
   
    -- truncate table #FaxDocs    
   

   -- Table Creation

    CREATE tABLE #FaxDocs ( [FaxDocID] INT , CustID INT,  RemoteCSID VARCHAR(20),  CSFileName VARCHAR(200))
   
    INSERT INTO #FaxDocs
   
    SELECT 1,1,'123ag','257831.PDF'
   
    UNION ALL 
   
    SELECT 2,1,'123yg','257837.PDF'
   
    UNION ALL 
   
    SELECT 3,2,'123g','257838.PDF'
   
    UNION ALL 
   
    SELECT 4,3,'123lg','257839.PDF'
   
    UNION ALL 
   
    SELECT 5,4,'123CK','257840.PDF'
   
   
   
    CREATE TABLE #Customer ( CustID INT , CFNAME VARCHAR(20),  CLNAME VARCHAR(200))
   
    insert into #Customer
   
    SELECT 1,'ABCNJSGHK', 'CDE'
   
    UNION ALL 
   
    SELECT 2,'CNJSGHK', 'CDE'
   
    UNION ALL 
   
    SELECT 3,'FGH', 'IJK'
   
    UNION ALL 
   
    SELECT 4,'LMN', 'OPQ'
   

   --- Search Bar KeyWord

    DECLARE @Search VARCHAR(MAX) =  'NJSGHK 257831.PDF'     
   
   
   
        --CREATE TABLE #WordsToLookUp (Item VARCHAR(257), Location INT, Sequence INT PRIMARY KEY)
   
   
   
            INSERT INTO #WordsToLookUp(Item,[Location],[Sequence])
   
            SELECT 'NJSGHK',0,1
   
            UNION ALL 
   
            SELECT '257831.PDF',8,2
   
   
   
        ----------------------------------------------------------
   
   
   
             SELECT * FROM #WordsToLookUp
   
            --truncate table  #WordsToLookUp
   
   
   
    --- Attempt 1
   
             SELECT  [FaxDocID], Customer.CustID ,ISNULL(Customer.CFNAME,'') AS [FName], ISNULL(Customer.CLNAME,'') AS [LName] ,
   
                Faxes.CSFileName
   
                FROM #FaxDocs Faxes
   
                LEFT JOIN #Customer Customer ON Faxes.CustID = Customer.CustID 
   
                left join #WordsToLookUp B on Customer.CFNAME  like '%'+ b.item+'%'
   
                left join #WordsToLookUp c on Customer.CLNAME  like '%'+ c.item+'%'
   
                left join #WordsToLookUp d on Faxes.RemoteCSID  like '%'+ d.item+'%'
   
                left join #WordsToLookUp e on Faxes.CSFileName  like '%'+ e.item+'%'
   
                WHERE Faxes.[FaxDocID] in (1,2,3,4)
   
   
   
    -- Attempt 2
   
                SELECT  [FaxDocID], Customer.CustID ,ISNULL(Customer.CFNAME,'') AS [FName], ISNULL(Customer.CLNAME,'') AS [LName] ,
   
                Faxes.CSFileName
   
                FROM #FaxDocs Faxes
   
                LEFT JOIN #Customer Customer ON Faxes.CustID = Customer.CustID 
   
                WHERE Faxes.[FaxDocID] in (1,2,3,4)
   
                and exists (select 1 from #WordsToLookUp b where Customer.CFNAME  like '%'+ b.item+'%')
   
                and exists (select 1 from #WordsToLookUp c where Customer.CLNAME  like '%'+ c.item+'%')
   
                and exists (select 1 from #WordsToLookUp d where Faxes.RemoteCSID  like '%'+ d.item+'%')
   
                and exists (select 1 from #WordsToLookUp e where Faxes.CSFileName  like '%'+ e.item+'%')
   

Answer 1

This will produce the result you want:

WITH Searches
AS
(
    SELECT F.FaxDocID, F.CustID
        ,COALESCE(C.CFNAME,'') AS FName
        ,COALESCE(C.CLNAME,'') AS LName
        ,F.RemoteCSID, F.CSFileName
        ,COUNT(1) OVER (PARTITION BY F.FaxDocID, F.CustID) AS Matches
    FROM #FaxDocs F
        LEFT JOIN #Customer C
            ON F.CustID = C.CustID
        CROSS APPLY
        (
            VALUES (C.CFNAME), (C.CLNAME), (F.RemoteCSID), (F.CSFileName)
        ) X (String2Search)
        LEFT JOIN #WordsToLookUp L
            ON X.String2Search LIKE '%' + L.item + '%'
    WHERE F.FaxDocID < 5
        AND L.Item IS NOT NULL
)
SELECT DISTINCT FaxDocID, CustID, FName, LName, RemoteCSID, CSFileName
FROM Searches
WHERE Matches = (SELECT COUNT(1) FROM #WordsToLookUp);

You may want to read you on trigrams:

https://sqlperformance.com/2017/09/sql-performance/sql-server-trigram-wildcard-search