Sql query for multiple names in a single search engine

Question

Can anyone help me to find out the SQL query for following scenario. I have a search box, which I want to search multiple names separated by spaces.

for example : "David Jones" which gives me the result of David's details and Jones details.

select 
    emp.cid as empcid,
    emp.name,
    emp.employeeno,
    info.employeeUniqueId, 
    info.agentId,
    info.empBankCode,
    info.accountNumber,
    info.ibanAccNo 
from tblemployee  emp,
    fk_tblUserEmployeeList f, 
    empinfo info 
where 
    info.employee = emp.cid 
    and emp.cid = f.employeeid 
    and f.userId = 1 
    and 
    (
        name like '%david%'  
        or emp.employeeno like '%david%'  
        or info.employeeUniqueId like '%david%'  
        or info.agentId like '%david%'  
        or info.empBankCode like '%david%'  
        or info.accountNumber like '%david%'
    )

I want include Jones inside search box also, then how will the like condition changes>

Possible duplicate of [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Thom A, Oct 08 '18 at 10:25
Just don't use the accepted answer, there are far better dataset approaches in the answers. — Thom A, Oct 08 '18 at 10:25

Zack · Answer 1 · 2018-10-08T13:09:31.823

This seems like a case for full-text search. After setting up full-text indices on your tblemployee, fk_tblUserEmployeeList, and empinfo tables, your query would look something like this:

SELECT
    emp.cid AS empcid,
    emp.name,
    emp.employeeno,
    info.employeeUniqueID,
    info.agentID,
    info.empBankCode,
    info.accountNumber,
    info.ibanAccNo
FROM dbo.tblemployee emp
    INNER JOIN dbo.fk_tblUserEmployeeList f ON
        f.employeeid = emp.cid
    INNER JOIN dbo.empinfo info ON
        info.employee = emp.cid
WHERE
    f.userID = 1
    AND 
    (   FREETEXT(Emp.*, 'david jones')
        OR FREETEXT(info.*, 'david jones')
    )

gives you this data:

+--------+-------+------------+------------------+---------+-------------+---------------+-----------+
| empcid | name  | employeeno | employeeUniqueID | agentID | empBankCode | accountNumber | ibanAccNo |
+--------+-------+------------+------------------+---------+-------------+---------------+-----------+
|      1 | David | NULL       | david            | david   | david       | david         | david     |
|      2 | Jones | NULL       | jones            | jones   | jones       | jones         | jones     |
+--------+-------+------------+------------------+---------+-------------+---------------+-----------+

Note that I changed your query to use the modern industry-standard join style.

Keep in mind that, to create a full-text index on a table, the table must have a single-column unique index. If one of your tables has a multi-column primary key, you'll have to add a column (see this question for more information).

A couple of notes about your naming conventions:

There's no need to preface table names with tbl (especially since you're not doing so consistently). There are loads of people telling you not to do this: See this answer as an example.
fk_tblUserEmployeeList is a bad table name: The prefixes fk and tbl don't add any information. What kind of information is stored in this table? I would suggest a more descriptive name (with no prefixes).

Now, if you don't want to go the route of using a full-text index, you can parse the input client-side before sending to SQL Server. You can split the search input on a space, and then construct the SQL accordingly.

How can I apply multiple input characters to query while am using 'like' operation? note, this search may have two or more names — Safwan Muhammed, Oct 09 '18 at 07:09
As I said above, you really want to use full text search, rather than `LIKE`. That being said, if you really want to use `LIKE`, I think your best option is doing it client-side (splitting your query string on spaces, and building your SQL dynamically). — Zack, Oct 09 '18 at 11:43
shown this error, Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'tblemployee' because it is not full-text indexed. So I have to do it in client side splitting? — Safwan Muhammed, Oct 10 '18 at 04:50
No, you have to set up a full-text index. Read the [first link](https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search) I posted in my article on how to set it up. — Zack, Oct 10 '18 at 12:17

score 0 · Answer 2 · answered Oct 08 '18 at 11:23

declare @SearchString varchar(200)='David Jones', @Word varchar(100)

declare @Words table (Word varchar(100))

-- Parse the SearchString to extract all words
while len(@SearchString) > 0 begin
    if charindex(' ', @SearchString)>0 begin 
        select  @Word = rtrim(ltrim(substring(@SearchString,0,charindex(' ', @SearchString)))),
                @SearchString = rtrim(ltrim(replace(@SearchString, @Word, '')))
    end
    else begin
        select  @Word = @SearchString,
                @SearchString = ''
    end

    if @Word != '' 
        insert into @Words select @Word
end

-- Return Results
select t.*
from MyTable t
    join @Words w on 
        ' ' + t.MyColumn + ' ' like '%[^a-z]' + w.Word + '[^a-z]%'

Sql query for multiple names in a single search engine

2 Answers2