SQL simple search functionality across multiple tables

Question

I'm using SQL Server 2012. I need to implement a search functionality using a single text field.

Let's say I have the following table:

--------------------------------------------------------------------------------
FIRSTNAME   LASTNAME    CITY        PROMOYEAR   EMPLOYOR    
--------------------------------------------------------------------------------
John        Doe         Boston      2005        Mc Donald   
Marc        Forestier   Bruxelle    2010        Private bank    
Céline      Durand      Paris       1999        Food SA     
Simon       Forestier   Toulouse    2001        Forestier SARL  
John        Smith       New York    1992        Events Org. 
Sonia       Grappe      Toulon      2010        Forestier SARL  
--------------------------------------------------------------------------------

Behavior is the following:

• All words (space separated) have to be searched over all the column.
• A LIKE should be applied for each word
• If only one word is searched, return all records that contains that word
• If several words are searched, return only records that contains the largest number of different words (see "forestier" example below)
• I need a single query, no TSQL

I tried a lot of things, but it is not as simple as it seems.

Some examples:

"John":

-------------------------------------------------------------------------------
FIRSTNAME   LASTNAME    CITY        PROMOYEAR   EMPLOYOR
-------------------------------------------------------------------------------
John        Doe         Boston      2005        Mc Donald
John        Smith       New York    1992        Events Org.
-------------------------------------------------------------------------------

"John Doe":

-------------------------------------------------------------------------------
FIRSTNAME   LASTNAME    CITY        PROMOYEAR   EMPLOYOR
-------------------------------------------------------------------------------
John        Doe         Boston      2005        Mc Donald
-------------------------------------------------------------------------------

"forestier":

-------------------------------------------------------------------------------
FIRSTNAME   LASTNAME    CITY        PROMOYEAR   EMPLOYOR
-------------------------------------------------------------------------------
Marc        Forestier   Bruxelle    2010        Private bank
Simon       Forestier   Toulouse    2001        Forestier SARL
Sonia       Grappe      Toulon      2010        Forestier SARL
-------------------------------------------------------------------------------

"for 2010 xelle":

FIRSTNAME   LASTNAME    CITY        PROMOYEAR   EMPLOYOR
--------------------------------------------------------------------------------
Marc        Forestier   Bruxelle    2010        Private bank
--------------------------------------------------------------------------------

This example uses a single table; in reality my 5 columns are from 4 different tables, so it is a little more complex to implement full text search!

Answer 1

How about adding another field e.g. a text field containing all the information from the other fields.

FIRSTNAME   LASTNAME    CITY        PROMOYEAR   EMPLOYOR   SEARCHFIELD
John        Doe         Boston      2005        Mc Donald  John Doe Boston 2005 Mc Donald

And make the search on this field. It's not elegant but it could work.

Adding below:

I don't think that SQL syntax supports all your need but you can make another workaround. Create a table which includes all the words you want to search:

create table searchtable
(
rowid int, --key to the id for the row in your table
mothertableName varchar(), -- name of the table if necessary
motherfieldName varchar(), -- name of field
word varchar() -- the actual word to be searchable
)

The search for the words and where they have the largest occurrence:

SELECT * FROM myTable WHERE id IN(
   SELECT rid as id, MAX(c) FROM (
      SELECT rowid as rid, COUNT(rowid) as c FROM Searchtable WHERE word IN ('john','doe')
   )
)

The above SQL will surely not work, but I hope you get the idea of what I am proposing. You should get one row with the largest numbers of you search words. But the 'IN' operator in SQL demands that you make some dynamically SQL.

As you write that you have tried nearly everything, I think that SQL can't do it alone.

Answer 2

This seems the job that Sql Full Text Indexing was designed for.

AFAIK Full text indexing doesn't work with numeric types, so you may need to add computed columns for any date or numeric types, e.g. if PromoYear is numeric:

ALTER TABLE MyTable
   ADD TextPromoYear AS CAST(PromoYear AS NVARCHAR(4))
   PERSISTED;

You'll need to setup a fulltext catalog in the database:

CREATE FULLTEXT CATALOG CAT_MyCat AS DEFAULT;

Assuming you have a primary key called PK_MyTable, create the full text index:

CREATE FULLTEXT INDEX ON MyTable(FirstName, LastName, City, TextPromoYear, 
                                 Employer) 
KEY INDEX PK_MyTable;

With some luck, you'll then be able to execute queries with [`CONTAINS`](http://technet.microsoft.com/en-us/library/ms187787.aspx) or `FREETEXT`, such as: SELECT * FROM mytable WHERE CONTAINS((FirstName, LastName, City, TextPromoYear, Employer), ' for OR 2010 OR xelle') The query syntax isn't exactly what you want, but you may be able to adapt the 'space separated' query you want into ` and ` as required by `CONTAINS`.

Edit

This isn't quite as easy as this. The wildcard * can only be used as a suffix, and you'll also need to aggregate the columns to search across all of them.

For complete terms:

SELECT *
FROM mytable
WHERE CONTAINS(*, 'John and Doe');

For partial searches you may need to resort to a hybrid using a vanilla LIKE for testing unknown prefixes (*xelle):

SELECT *
FROM mytable
WHERE CONTAINS(*, '"for*"  and 2010') AND SearchableComputedColumn like '%elle%';

Updated SqlFiddle here

Answer 3

Here is a solution.

I have limited the search to 6 words. For each word, I check if it exists in the concatenated columns. I get a "score" for each record by adding +1 each time a word is found in it. I return the records that have the best score.

The function :

CREATE FUNCTION [dbo].[SEARCH_SINGLE] (
    @langId INT = 4,
    @searchString VARCHAR(MAX) = NULL
)
RETURNS TABLE
AS
RETURN
WITH words AS (
    SELECT Name as Val, ROW_NUMBER() OVER(ORDER BY Name) as Num FROM [dbo].splitstring(@searchString, ' ')
),
results AS (
    SELECT DISTINCT
        ...
        CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 1 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
        CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 2 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
        CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 3 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
        CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 4 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
        CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 5 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
        CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 6 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END as Nb
    FROM
        ...
    WHERE
        ...
)
SELECT 
    ...
FROM
    results
WHERE
    Nb = (SELECT MAX(Nb) FROM results)
    AND Nb <> 0

Comments?