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?

"We are not considering full-text search for the moment!". Why not? — gbn
– gbn, Commented Mar 4, 2014 at 15:15
I was asked to create a simple search function, without doing too much modification at database level (index, etc.). As the search is done across multiple table, what I read over the net let me think that it is a lot of work. — Yann39
– Yann39, Commented Mar 4, 2014 at 15:26
I would hardly consider your requirement to be simple. Trying to parse a single entry and check for spaces, wild cards, etc, and then know what column to apply various parts to sounds like misery. Is modifying your front end an option? This would be way easier if you could have your users select what field they want to search on and enter one word. Give them the ability to add more search criteria if they want to search multiple fields. — Andrew
– Andrew, Commented Mar 4, 2014 at 15:41
We already have a "Custom search" where users can select fields they want to search on. But requirements is to have a second "simple search" allowing users to do a search using a single text field. To be honest, I have currently a working solution using full C#, with LINQ and Entity Framework, but I would like to do it in a database way. — Yann39
– Yann39, Commented Mar 4, 2014 at 17:09

Yann39 · Accepted Answer · 2014-03-14 12:23:35Z

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.

Collectives™ on Stack Overflow

Search functionality with LIKE operator over several columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related