CODEWITHSUNDEEP
asp.netsqlsql-server-2008
James Hay
James Hay

Reputation: 7325

Using LIKE in a SQL query

I'm making a search box to search the name of users in my database. The database has a firstname and lastname field. I want to search these fields to find users who match the query. So I came up with this:

SELECT *
FROM user
WHERE (firstname + ' ' + lastname) LIKE ('%' + @query + '%')

It works but I'm not sure if thats the best way to do it. Especially as the database gets bigger. Is there a better solution or will this suffice?

Upvotes: 2

Views: 1232

Answers (3)

Asif Mukhida
Asif Mukhida

Reputation: 142

to get all possible search users best way is to compare it individually for getting user by inserted search text but as it wont include full name in search. So to include full name we are considering full name also by taking a space in between.

(First_Name LIKE '%' + @SearchText + '%') OR (Last_Name LIKE '%' + @SearchText + '%') OR (First_Name + ' ' + Last_Name LIKE '%' + @SearchText + '%')

Upvotes: 0

Eric
Eric

Reputation: 3344

Take a look at SQL server's Full Text Indexing capabilities: http://msdn.microsoft.com/en-us/library/ms142571.aspx

It may be a bit of refactoring but it's much more efficient to search an index as opposed to row by row.

If you outgrow that, you can always consider something like Lucene.Net. It has much more flexibility in terms of query operators. It's a completely separate indexing and query engine, so it would likely take a fair bit of refactoring. Just something to consider.

Upvotes: 4

Simon Wang
Simon Wang

Reputation: 2963

add a Computed Columns in db: http://msdn.microsoft.com/en-us/library/ms191250.aspx and then query against that column

Upvotes: 0

Related Questions