SQL Full text Search with phrases, prefix_term on multiple columns

My application is storing Names of Customers in 3 Columns, but the search triggered using a single parameter. I am trying to implement a FULLTEXT Search to effectively search in this customer database.

I am looking for the name

  • sql query to calculate the difference between two dates of different columns and adjacent rows
  • How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  • Getting a Subset of Records along with Total Record Count
  • Update column value of mutiple tables, only 1 row affected
  • How can I make a REPLACE PATTERN in SQL?
  • Is there a way to do another query within the insert query?
  • RIMA HUSSEIN

    I used the following TSQL to search with the starting word.

    SELECT 
        REM_ID, REM_FNAME, REM_MNAME, REM_LNAME 
    FROM 
        dbo.tblM_REMMASTER 
    WHERE 
        CONTAINS((REM_FNAME , REM_MNAME, REM_LNAME) ,'"RIMA*"')
    

    The query works as expected providing me with good search result.

    enter image description here

    This scenario does not provide me with any result if I provide an updated query with phrases.

    Sample test queries executed to try and resolve the scenario.

    SELECT 
        REM_ID, REM_FNAME, REM_MNAME, REM_LNAME 
    FROM 
        dbo.tblM_REMMASTER 
    WHERE 
        CONTAINS((REM_FNAME , REM_MNAME, REM_LNAME), '"rima*" AND "*hussein"') 
    
    SELECT 
        REM_ID, REM_FNAME, REM_MNAME, REM_LNAME  
    FROM 
        dbo.tblM_REMMASTER 
    WHERE 
       CONTAINS(REM_FNAME  ,'"RIMA HUSSEIN"')
    
    SELECT 
        REM_ID, REM_FNAME, REM_MNAME, REM_LNAME 
    FROM 
        dbo.tblM_REMMASTER 
    WHERE 
        CONTAINS((REM_FNAME , REM_MNAME, REM_LNAME)  ,'NEAR ((RIMA,HUSSEIN),MAX,TRUE)')
    

    Has anyone faces this issue before or what kind of solution logic can be implemented under this specific scenario ?

    Sample query tested in the live environment without fulltext:

    SET @NAME=N'RIMAHUSSEIN'
    
    SELECT  
        REM_ID, REM_FNAME, REM_MNAME, REM_LNAME 
    FROM 
        dbo.tblM_REMMASTER WITH (READPAST) 
    WHERE
        ((LTRIM(RTRIM(REM_FNAME)) +  LTRIM(RTRIM(REM_MNAME)) + LTRIM(RTRIM(REM_LNAME)) LIKE '%' + @NAME + '%'))
    

    2 Solutions collect form web for “SQL Full text Search with phrases, prefix_term on multiple columns”

    We have application that must search in almost every column in a table, so we create a view WITH SCHEMABINDING and CLUSTERED INDEX with structure like this:

    CREATE VIEW search_view
    WITH SCHEMABINDING
    AS
    SELECT  id,
            column1 + ' ' + column2 + etc as search_text
    FROM table
    
    CREATE CLUSTERED INDEX ci_id ON search_view (id);
    

    And put full text index on search_text column.
    Then we just:

    SELECT ...
    FROM table t
    INNER JOIN CONTAINSTABLE(search_view, search_text, '"word1*" AND "word2*" ... ') AS sv
        ON t.ID = sv.ID
    

    Give it a try..

    SELECT * FROM dbo.tblM_REMMASTER WHERE CONTAINS((REM_FNAME,REM_MNAME, REM_LNAME) ,'"RIMA*"') 
    AND  CONTAINS((REM_FNAME , REM_MNAME, REM_LNAME), '"*hussein"') 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.