SQL Server – Text Search on Multiple Columns Without FULL TEXT Search

sql serversql-server-2008sql-server-2016

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+'%')

Best Answer

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

Related Question