Search functionality with LIKE operator over several columns

Question

• I have a search field allowing users to enter space-delimited words.
• I need to search each word over 5 columns from several tables in the database.
• I want to create a database function that can be called from my C# web service (Entity Framework).

For the moment, I use this:

CREATE FUNCTION [dbo].[SEARCH_SINGLE] (
    @langId INT = 4,
    @searchString VARCHAR(MAX) = NULL
)
RETURNS TABLE
AS
RETURN
    WITH words AS (
        SELECT * FROM [dbo].splitstring(@searchString, ' ')
    )
    SELECT DISTINCT
        ...
    FROM
        ...
    WHERE
        ...
        AND (
            EXISTS(SELECT t2.Name FROM words t2 WHERE a.[FIRSTNAME] like '%'+t2.Name+'%')
            OR EXISTS(SELECT t2.Name FROM words t2 WHERE a.[LASTNAME] like '%'+t2.Name+'%')
            OR EXISTS(SELECT t2.Name FROM words t2 WHERE c.[CITY] like '%'+t2.Name+'%')
            OR EXISTS(SELECT t2.Name FROM words t2 WHERE j.[PROMO_YEAR] like '%'+t2.Name+'%')
            OR EXISTS(SELECT t2.Name FROM words t2 WHERE e.[EMPLOYOR] like '%'+t2.Name+'%')
        )

It works but it returns all records matching one of the entered words. I need to return records that match all the words in at least one column.

For example if we consider only firstname and lastname :

• "John" should return "John Doe", "John Bieber", "John Daney", etc.
• "John D%" should return "John Doe" and "John Daney"
• "John Doe" should return only "John Doe".

Actually, for a search like "John Doe McDonald", I need to simulate a WHERE like this :

WHERE 
    (
        a.[FIRSTNAME] like '%John%'
        OR a.[LASTNAME] like '%John%'
        OR c.[CITY] like '%John%'
        OR j.[PROMO_YEAR] like '%John%'
        OR e.[EMPLOYOR] like '%John%'
    ) 
    AND (
        a.[FIRSTNAME] like '%Doe%'
        OR a.[LASTNAME] like '%Doe%'
        OR c.[CITY] like '%Doe%'
        OR j.[PROMO_YEAR] like '%Doe%'
        OR e.[EMPLOYOR] like '%Doe%'
    )
    AND (
        a.[FIRSTNAME] like '%McDonald%'
        OR a.[LASTNAME] like '%McDonald%'
        OR c.[CITY] like '%McDonald%'
        OR j.[PROMO_YEAR] like '%McDonald%'
        OR e.[EMPLOYOR] like '%McDonald%'
    )

Ideally, if a word is not found in the 5 columns in the whole database, just ignore it ("John Doe sjdhf67df" should return "John Doe"...).

I have tried to build the query dynamically but I get some problem to return the results, as we cannot use EXEC in functions...

We are not considering full-text search for the moment!

How can I modify my function to get expected results?

Answer 1

I finally came back to the following solution :

CREATE FUNCTION [dbo].[SEARCH_SINGLE] (
    @langId INT = 4,
    @searchString VARCHAR(MAX) = NULL
)
RETURNS TABLE
AS
RETURN
    WITH
words AS (
    SELECT * FROM [dbo].splitstring(@searchString, ' ')
),
results AS (
    SELECT DISTINCT
        a.[ID] as Id,
        a.[LASTNAME] as LastName,
        a.[FIRSTNAME] as FirstName,
        d.[COUNTRY_LABEL] as CountryLabel,
        c.[CITY] as City,
        j.[PROMO_YEAR] as PromoYear,
        CASE WHEN EXISTS(SELECT t2.Name FROM words t2 WHERE a.[FIRSTNAME] like '%'+t2.Name+'%') THEN 1 ELSE 0 END +
        CASE WHEN EXISTS(SELECT t2.Name FROM words t2 WHERE a.[LASTNAME] like '%'+t2.Name+'%') THEN 1 ELSE 0 END +
        CASE WHEN EXISTS(SELECT t2.Name FROM words t2 WHERE c.[CITY] like '%'+t2.Name+'%') THEN 1 ELSE 0 END +
        CASE WHEN EXISTS(SELECT t2.Name FROM words t2 WHERE j.[PROMO_YEAR] like '%'+t2.Name+'%') THEN 1 ELSE 0 END +
        CASE WHEN EXISTS(SELECT t2.Name FROM words t2 WHERE e.[EMPLOYOR] like '%'+t2.Name+'%') THEN 1 ELSE 0 END as Nb
    FROM
        ...
    WHERE
        ...
)
SELECT 
    Id,
    LastName,
    FirstName,
    CountryLabel,
    City,
    PromoYear,
FROM
    results
WHERE
    Nb = (SELECT MAX(Nb) FROM results)
    AND Nb <> 0

• I search for each word on each column.
• I give a "score" for each record.
• I return only records that have the best score.

It seems to cover all my requirements.